Forum Discussion
Use text in a cell to reference the name of an Array for INDEX
On 2013 you shall use it as array function, i.e. using Ctrl+Shift+Enter to enter the formula. It is explained in details here TRANSPOSE function - Office Support (microsoft.com)
If you prefer INDEX and drag cells to the right, it'll be like
=INDEX( INDIRECT(A1), COLUMN(A$1) )
Do you mean to put that in place of ARRAY?
Like this?
=INDEX(TRANSPOSE(INDIRECT(A1),COLUMN(A$1)))
This didn't work for what I want. I need the content of cell A1 to tell the index function what the name of the array is.
I mean if you have named range in column A like this
this formula returns it in first column of next sheet
It slightly depends on your Excel version how to use this formula.
- Ok I understand what you're saying.
I made a test sheet and typed it in just as you have shown and I get a #VALUE!
I'm using Office Pro Plus 2013.
Thank you!On 2013 you shall use it as array function, i.e. using Ctrl+Shift+Enter to enter the formula. It is explained in details here TRANSPOSE function - Office Support (microsoft.com)
If you prefer INDEX and drag cells to the right, it'll be like
=INDEX( INDIRECT(A1), COLUMN(A$1) )Wonderful!
This is what I got to do exactly what I want. I can drag down for the next array and drag across to fill in the values.On sheet 2 I used this forumla
=INDEX(INDIRECT($A1),COLUMN('SHEET1'!A$1))
Thank you for the quick help. I appreciate it. I don't think I would have quite figured this out on my own.