Forum Discussion

Phishdawg's avatar
Phishdawg
Brass Contributor
Apr 05, 2023

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...
excel
Patrick2788's avatar
Patrick2788
Silver Contributor
Apr 07, 2023
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.
Phishdawg's avatar
Phishdawg
Brass Contributor
Apr 07, 2023
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?
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Apr 07, 2023

    Phishdawg 

     

     

    =LET(
    header, Sheet!A1:R1,
    stack, VSTACK(Sheet4:Sheet6!A2:R60000),
    filtered, FILTER(stack, TAKE(stack, , 1) <> ""),
    VSTACK(header, filtered)
)
    • Phishdawg's avatar
      Phishdawg
      Brass Contributor
      Apr 07, 2023

      Patrick2788
      Edit -
      Got it! The second line was missing a number for the sheet.
      *******************************************************************
       
      How do I edit the formula so that the heading doesn't repeat.

       

       

      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        Apr 07, 2023
        Updated above*

Resources