Create a master sheet using excel that updates as others are changed.

%3CLINGO-SUB%20id%3D%22lingo-sub-1579371%22%20slang%3D%22en-US%22%3ECreate%20a%20master%20sheet%20using%20excel%20that%20updates%20as%20others%20are%20changed.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1579371%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%26nbsp%3B%20not%20the%20most%20tech%20savvy%20guy%20out%20here%20when%20it%20comes%20to%20excel%20but%20I%20need%20your%20help.%20I'm%20used%20to%20basic%20formulas%20but%20i've%20not%20been%20able%20to%20find%20a%20way%20around%20this%20task.%20I%20have%20a%20workbook%20with%20various%20sheets%20on%20it.%20My%20primary%20goal%20is%20to%20ensure%20that%20as%20each%20sheet%20is%20filled%2C%20the%20data%20there%20is%20reflected%20on%20the%20master%20sheet.%20I%20have%20attached%20the%20spreadsheet%2C%20if%20it%20is%20a%20formula%20required%20or%20working%20with%20Query%2C%20please%20advise%20on%20how%20to%20get%20this%20spreadsheet%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1579371%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1579692%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20master%20sheet%20using%20excel%20that%20updates%20as%20others%20are%20changed.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1579692%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F755200%22%20target%3D%22_blank%22%3E%40PNEME82%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ein%20most%20cases%2C%20duplicating%20data%20is%20not%20required%20if%20you%20follow%20the%20principles%20of%20good%20data%20architecture.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EInstead%20of%20entering%20data%20into%20the%20numerous%20sheets%2C%20consider%20entering%20data%20just%20ONCE%2C%20in%20the%20Master%20sheet.%26nbsp%3B%20Then%20you%20can%20set%20up%26nbsp%3B%20a%20report%20to%20create%20filtered%20lists%20for%20different%20groups%20of%20people%2C%20based%20on%20the%20group%20they%20belong%20to.%20You%20need%20to%20add%20a%20column%20to%20the%20master%20sheet%2C%20then%20add%20all%20the%20data%20to%20that%20sheet.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENext%2C%20create%20a%20pivot%20table%20with%20all%20the%20columns%20you%20want%20to%20show.%20Use%20the%20new%20group%20column%20as%20a%20slicer.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20you%20only%20need%20ONE%20sheet%20to%20report%20on%20the%20different%20groups%20of%20people%20and%20no%20data%20needs%20to%20be%20duplicated%20or%20copied.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1628871%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20master%20sheet%20using%20excel%20that%20updates%20as%20others%20are%20changed.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1628871%22%20slang%3D%22en-US%22%3EDear%20Ingeborg%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20your%20response..%20As%20stated%20earlier%2C%20I%20am%20a%20novice%20with%20Excel%20and%20had%20hoped%20some%20sort%20of%20simple%20formula%20would%20help.%20If%20there%20is%20any%20other%20way%20as%20I'm.not%20used%20to%20pivots%20etc%2C%20it'll%20be%20of%20greater%20help.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you!%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi All,

 

I  not the most tech savvy guy out here when it comes to excel but I need your help. I'm used to basic formulas but i've not been able to find a way around this task. I have a workbook with various sheets on it. My primary goal is to ensure that as each sheet is filled, the data there is reflected on the master sheet. I have attached the spreadsheet, if it is a formula required or working with Query, please advise on how to get this spreadsheet to work.

 

Thank you!

2 Replies
Highlighted

Hello @PNEME82 ,

 

in most cases, duplicating data is not required if you follow the principles of good data architecture. 

 

Instead of entering data into the numerous sheets, consider entering data just ONCE, in the Master sheet.  Then you can set up  a report to create filtered lists for different groups of people, based on the group they belong to. You need to add a column to the master sheet, then add all the data to that sheet.

 

Next, create a pivot table with all the columns you want to show. Use the new group column as a slicer. 

 

Now you only need ONE sheet to report on the different groups of people and no data needs to be duplicated or copied.

Highlighted
Dear Ingeborg,

Thanks for your response.. As stated earlier, I am a novice with Excel and had hoped some sort of simple formula would help. If there is any other way as I'm.not used to pivots etc, it'll be of greater help.

Thank you!