Forum Discussion
Dragging formulas, including tab names
Hi, I have set up a sheet gathering data from seperate tabs in the same workbook (same cells) - I am looking to copy the data down and have the tab name in the formula move to the next tab (like you would do with any other formula) - does anyone know if this is possible? I am currently doing it manually for 500 lines....
You could do the following:
In A2 and down, enter the names of the worksheets.
In B2, enter the formula
=INDIRECT("'"&A2&"'!D4")where D4 is the cell you want to refer to.
Fill down.
3 Replies
That's brilliant - thanks!
While you can use INDIRECT() and drag down the formula, you can also use dynamic spill array formulas to gather data from different tabs automatically. Give a try to the following formula. You may download the attached file to see a example.
=DROP(REDUCE("",A4:A6,LAMBDA(a,x,VSTACK(a,INDIRECT("'"&x&"'!A1:J10")))),1)You could do the following:
In A2 and down, enter the names of the worksheets.
In B2, enter the formula
=INDIRECT("'"&A2&"'!D4")where D4 is the cell you want to refer to.
Fill down.
