Share via

Quickest way to autopopulate rows from multiple sheets into a mastersheet

Anonymous
2023-09-14T14:23:17+00:00

Hi all. I'm working on a project, and I'm stuck with figuring out an easy consolidation for a master report. I have 10 sheets representing 10 offices who will add rows to their respective sheets. I want the final sheet to be a Master Sheet that auto populates the rows from all ten sheets. So, if in the Riverhead and Sayville sheet a new row is added the rows would populate on the master sheet. I have tried to create a pivot table and select the tables and am getting an error and then tried to consolidate data, and I have had no luck. Any suggestions?

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-09-14T14:47:30+00:00

    You may try Vstack& filter.

    =VSTACK(

    FILTER(Sheet1!A2:I1000,Sheet1!A2:A1000<>""),

    FILTER(Sheet2!A2:I1000,Sheet2!A2:A1000<>""),

    FILTER(Sheet3!A2:I1000,Sheet3!A2:A1000<>""),

    FILTER(Sheet4!A2:I1000,Sheet4!A2:A1000<>""),

    FILTER(Sheet5!A2:I1000,Sheet5!A2:A1000<>""),

    FILTER(Sheet6!A2:I1000,Sheet6!A2:A1000<>""),

    FILTER(Sheet7!A2:I1000,Sheet7!A2:A1000<>""),

    FILTER(Sheet8!A2:I1000,Sheet8!A2:A1000<>""),

    FILTER(Sheet9!A2:I1000,Sheet9!A2:A1000<>""),

    FILTER(Sheet10!A2:I1000,Sheet10!A2:A1000<>""))

    Data in sheet 1

    Result in sheet master.

    0 comments
  2. Ashish Mathur 101K Reputation points Volunteer Moderator
    2023-09-14T23:15:21+00:00

    Hi,

    Follow the steps shown in this video - Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks - YouTube.

    Hope this helps.

    0 comments