Forum Discussion
Solved
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 ...
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.
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)