Forum Discussion
Filtering Data onto one Sheet
I want to filter the data from several different sheets onto one sheet if cell A2="Open". I know how to use the Filter function for just one sheet, however, I can't seem to figure out how to get it to work for multiple.
Sheet names that data will be pulled from: DL, DCA, WDW - MK, WDW - Epcot, WDW - HS, WDW - AK, USO - Florida, USO - IA, USO - VB, BGT, TDL, TDS, DLP, WDSP
The information in each sheet is organized as follows: Column A (Status), Column B (Ride), Column C (Warp), Column D (Points), Column E (Time Start), Column F (Time End), Column G (Length), and Column H (No. of Riders)
The information that I want to show on Sheet 1 is the ride name, it's warp, the points, the length of the ride, and the number of riders. (The number of rides on each sheet varies.)
Additionally, I'd like to be able to list the name of the sheet each value is from in Column A in Sheet 1. (i.e., if It's a Small World in DL is open, then Column A should say "DL", Column B should say "It's a Small World", Column C should say "/iasw", Column D should say "134", Column E should say "13:25" (13 minutes and 25 seconds), and Column F should say "10".)
Below is a picture some of the information on the DL sheet:
=VSTACK(HSTACK(INDEX(Sheet2!J1,SEQUENCE(ROWS(FILTER(Sheet2!B2:H17,Sheet2!A2:A17="OPEN")),1,1,0)),FILTER(Sheet2!B2:H17,Sheet2!A2:A17="OPEN")),HSTACK(INDEX(Sheet3!J1,SEQUENCE(ROWS(FILTER(Sheet3!B2:H17,Sheet3!A2:A17="OPEN")),1,1,0)),FILTER(Sheet3!B2:H17,Sheet3!A2:A17="OPEN")))For two sheets you can try this formula which can be expanded to many more sheets.
The table name is in cell J1 in each sheet. This cell is referenced in the formula in order to return the sheetname in column A of the summary sheet.
An alternative to the new formulas VSTACK, HSTACK, FILTER and SEQUENCE could be a transformation with Power Query.
1 Reply
=VSTACK(HSTACK(INDEX(Sheet2!J1,SEQUENCE(ROWS(FILTER(Sheet2!B2:H17,Sheet2!A2:A17="OPEN")),1,1,0)),FILTER(Sheet2!B2:H17,Sheet2!A2:A17="OPEN")),HSTACK(INDEX(Sheet3!J1,SEQUENCE(ROWS(FILTER(Sheet3!B2:H17,Sheet3!A2:A17="OPEN")),1,1,0)),FILTER(Sheet3!B2:H17,Sheet3!A2:A17="OPEN")))For two sheets you can try this formula which can be expanded to many more sheets.
The table name is in cell J1 in each sheet. This cell is referenced in the formula in order to return the sheetname in column A of the summary sheet.
An alternative to the new formulas VSTACK, HSTACK, FILTER and SEQUENCE could be a transformation with Power Query.