Share via

Three Excel sheets - master sheet w/ updates?

Anonymous
2024-04-05T12:38:43+00:00

Hi!

I was wondering if you could help me, any info would be greatly appreciated.

We have three different excel sheets, currently in separate workbooks, that are in Sharepoint and shared with three different groups of individuals. Currently, three different individuals own/created these sheets, and I am one of them.  If I merged them all into one master spreadsheet, how would i extract the sheets into different tabs based on the "group name?" And would it be possible for this to be done in a way that if the master sheet was updated, the corresponding tab would also update?

Or is there an easier way to somehow merge these into one master sheet so that if there's an update in one sheet, it automatically updates the master sheet?

Thank you!

Microsoft 365 and Office | Excel | Other | 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
    2024-04-05T13:27:58+00:00

    Hello Jackie,

    * I am a Microsoft user like you, providing solutions to community members; I am NOT a Microsoft employee.

    There are several ways to achieve what you just outlined; but here's how I'll go about it:

    1. Start by copying the data from each of the three individual sheets into the master sheet. You can copy the entire sheet or just the relevant data range.
    2. After pasting the data into the master sheet, create separate tabs for each group. You can name these tabs based on the group names. Then, move the corresponding data into each tab.
    3. To ensure that the master sheet updates automatically when changes are made to the individual sheets, you can use formulas or Power Query.
    • Formulas (for smaller datasets):
      • If the data volume is not too large, you can use formulas like VLOOKUP, INDEX-MATCH, or IF statements to reference the data from the individual sheets. This way, any changes made to the individual sheets will automatically reflect in the master sheet.
    • Power Query (for larger datasets):
      • If the data volume is large and more complex, you can use Power Query to import and merge data from multiple sources. Power Query allows you to create queries that fetch data from SharePoint directly. You can then merge, append, or transform this data as needed before loading it into the master sheet.
    1. If you're using Power Query to import data, you can set up a schedule for data refresh so that the master sheet updates automatically at specified intervals. This ensures that any changes made to the individual sheets are reflected in the master sheet without manual intervention.
    2. Once you have set up the master sheet with the data from all three groups and ensured that it updates automatically, you can share this master sheet with the respective groups. They will be able to view the data relevant to their group on separate tabs.

    Hope this helps you.

    • Stephen N.

    2 people found this answer helpful.
    0 comments