Absolute Cell Reference issue

Dexter_

New Member
Joined
Jul 1, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,496
Office Version
  1. 365
Platform
  1. Windows
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_

New Member
Joined
Jul 1, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Wow!

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

Thank you, for putting me on next level.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,131
Messages
5,628,885
Members
416,349
Latest member
salmanb74

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top