Aug 25 2021 12:26 PM
Hello,
I am trying to modify a manual workbook used in my company.
I am using INDEX(Array,Column(A$1)) and dragging it across to bring info in from an array.
I have to do this with a ton of arrays but the rows where the data ends up already happen to contain the name of my arrays.
For example on sheet 1 I have an array named Apples with all different apple prices in 1 column.
On sheet 2 I have a row where cell A1 is Apples then I need to fill in apple prices across from B1 to Z1
So I do this =INDEX(Apples,Column(A$1)) and drag it over.
Can I replace the array argument with something that will pull the name "Apples" and use it as the name of the array in the formula like....
=INDEX("Name from A1 recognized as the name of the array", Column(A$1))
Please let me know if my request is clear.
Thanks for any help!
Aug 25 2021 12:32 PM
Aug 25 2021 12:40 PM
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.
Aug 25 2021 01:10 PM
No. Just use the formula as @Sergei Baklan 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.
Aug 25 2021 01:12 PM
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.
Aug 25 2021 01:29 PM
Aug 25 2021 01:42 PM
SolutionOn 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) )
Aug 26 2021 05:09 AM
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.
Aug 26 2021 05:17 AM
@ANEX_Brett , you are welcome, glad it helped
Aug 25 2021 01:42 PM
SolutionOn 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) )