How to reference every cell of another tab using indirect

0
votes

enter image description hereI want to create a tab that will display all the cell values of another tab based on the tab name provided on a cell.

I have played around with the indirect function, but I cannot write a formula so that by dragging down and right it will access the cells. 

=INDIRECT("'"&$A$1&"'!A1")

This part &$A$1& is getting the name of the tab. If I drag down this formula, the last A1 does not change though.

3
excelexcel-formula

3 Answers

2
votes

You can use the formula ="Sheet2!A1" in Sheet1 Cell A1 and drag down, then across. I tried to find a faster way to paste it into every cell, but I had to force quit the application because it used all of my available memory. You might think about whether you actually need every cell to be duplicated into the second sheet. Good luck!

Edit: Just realized you were dead set on using Indirect. This should work.

=INDIRECT("'Sheet2'!"&CELL("address",A1))
1
votes

Simply use following formula

=INDIRECT($A$1&"!A" & ROW(1:1))
1
votes

Try building the string like this. The final A1 will work as you want it to.

=INDIRECT(CONCATENATE("'",$A$1,"'!", CELL("address",A1)))