Absolute Cell Reference issue

[Dexter_]

Hello,

Could you help me to create a proper working formula for following scenario. I have MS Excel version 2016.
1.
On a Sheet1 I have table named "T_Products" starting from A1 cell. Each column has headers.
So A column in Sheet1 looks following:
cell value comment
A1, Index title of the first column of the "T_Products"
A2 AA23 first value of the column "Index"
A3 AB34 second value of the column "Index"
etc.

2.
I would like to reference all column "Index" values on the Sheet2 starting from A1 cell.

3.
If I use absolute formula in Sheet2 A1 cell
=T_Products[Index]:[Index] I get
cell value comment
in A1 #VALUE! It refers to "T_Products" header value*
in A2 AA23
in A3 AB34


*but need the formula to show in A1 cell "AA23" value
in cell A2 "AB34 " etc.

Thank you in advance.

Kiril

 

[jasonb75]

Using =T_Products[Index]:[Index] will only work if the formula is in the same row as the expected result, because you want the result from the cell 1 row below the formula you need to use something like

=INDEX(T_Products[Index]:[Index],ROWS(A$1:A1))

 

[Dexter_]

Wow!

Thank you for prompt reply. I never thought to use absolute structured reference with INDEX.

Thank you, for putting me on next level.