CODEWITHSUNDEEP
excelvba
shafac14
shafac14

Reputation: 15

Row number for Excel table reference

I have an Excel file with few tables, but on different sheets and positions. The summary table is referencing each one. The problem is that I get #VALUE! errors when using this formula: 

=Table1[@Column1]

As shown on the example below it works when two tables are in the same row, but when the same table is moved away or down, it's stops working. Any ideas?

enter image description here

Upvotes: 0

Views: 3170

Answers (2)

QHarr
QHarr

Reputation: 84475

Problem:

With the following structured reference

=Table1[@Column1]

You are doing the # This Row context i.e. it will only work correctly if your additional table is aligned on the same rows.

If you line the tables up you will see it works.

Solution:

You can cheat as follows by referencing both tables and using the OFFSET and ROW function:

=OFFSET(Table1,ROW(F9)-ROW(Table2),,1,1)

Notes:

1) ROW(F9)-ROW(Table2) generates the number of rows to offset by.

2) =CELL("address",Table1) gives you the first data body range cell address in the table.

Cheat

You can use similar idea with Index

=INDEX(Table1[Column1], ROWS($E$17:E17))

Upvotes: 1

DirtyDeffy
DirtyDeffy

Reputation: 507

I'm not quite sure i understood your question correctly.
This however, works no matter where the tables are placed:

Sub Table_Reference()
Dim rng As Range
Set rng = Range("Table1[Column1]")

Range("Table2[Column1]").Value = rng.Value

End Sub

You might want to specify the worksheet you're working in at the beginning of the code.

Edit:

As VBA is unwanted by the OP i suggest using =INDEX(Table1[Column1]; 1)
This would reference the first row of data in the specified table and column eg. "Table1" and "Column1".
Referencing the next row would be =INDEX(Table1[Column1]; 2) and so on.
However you would need to type this manually into each cell that's supposed to have a reference.

Upvotes: 0

Related Questions