Forum Discussion
Moving Items from Sheet to Sheet
I am in receipt of an Excel workbook with ten sheets that each have more than 50,000 lines of data. I have made a tables of all data on each sheet. The client wants all data on one sheet (for o...
If you have Excel 365, you can use VSTACK:
The sample workbook is rolling up sheets 1 to 3.
=LET(
header, Sheet1!A1:E1,
stack, VSTACK(Sheet1:Sheet3!A2:E100),
filtered, FILTER(stack, TAKE(stack, , 1) <> ""),
VSTACK(header, filtered)
)
- You've got a circular reference. You'll want to pull the 'header' from any of the sheets but not from the sheet with the formula.
- Remove headers from all sheets except sheet 1.
Do I need to edit the formula? If yes, how, what should be changed or removed from formula?=LET( header, Sheet!A1:R1, stack, VSTACK(Sheet4:Sheet6!A2:R60000), filtered, FILTER(stack, TAKE(stack, , 1) <> ""), VSTACK(header, filtered) )