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.

         

         

Resources