Forum Discussion

AmberGI's avatar
AmberGI
Copper Contributor
Aug 04, 2023

Google Sheets vs Excel

I have a workbook that has 5 sheets. On page 6, I want the data from the first 5 sheets to appear and filter in alphabetical order. In google sheets this is the formula

=SORT({'Page 1'!A8:AV; 'Page 2 '!A7:AV; 'Page 3'!A8:AV; 'Page 4 '!A8:AV; 'Page 5'!A8:AV}) and it works perfectly. It stacks everything together row by row, no spaces between pages and it sorts in alphabetical order. What would the excel formula be to create the same effect? I tried putting this formula into excel and I got an error. I know its not SORT, but I'm not sure what it should be. Please help! This is the a screen shot of my overalls page that works in Google sheets. How do I replicate this inside of Excel? 

Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Aug 04, 2023

    AmberGI 

    You'll have to use a function capable of accepting a 3D reference. SORT will not accept a 3D reference but VSTACK will.

     

    For example:

    Stack 3 sheets, pull blanks rows, and then sort.

    =LET(
    Stack, VSTACK(Sheet1:Sheet3!A1:F100),
    filtered, FILTER(Stack, TAKE(Stack, , 1) <> ""),
    SORT(filtered)
)

     

Resources