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) )
No. Just use the formula as SergeiBaklan has shown it. It is an array formula that interprets the contents of A1 as a range reference and transposes the result. You may need CSE to display the resulting array in older versions of Excel.
