Master work sheet that's not affected by 'sorting' data from Multiple sheets that populate it?

%3CLINGO-SUB%20id%3D%22lingo-sub-3093045%22%20slang%3D%22en-US%22%3EMaster%20work%20sheet%20that's%20not%20affected%20by%20'sorting'%20data%20from%20Multiple%20sheets%20that%20populate%20it%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3093045%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3EI've%20had%20some%20great%20help%20from%20here%20in%20the%20past%2C%20so%20hoping%20you%20might%20be%20able%20to%20help%20again!%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20a%20staff%20plan%20document%20that%20avoids%20me%20having%20to%20have%20multiple%20excel%20files%20for%20the%20same%20information.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20a%20excel%20file%20with%20multiple%20sheets%20ie%20Tab%201%2C%20Flights%20(has%20flight%20details%20and%20incoming%20and%20out%20going%20dates%2C%20procurement%20etc)%2C%20Tab%202%2C%20contact%20details%2C%20Tab%203%20Other%20info%20like%20visa%20status%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20know%20if%20this%20is%20the%20correct%20way%20make%20a%20master%20sheet%20but%20I%20have%20populated%20it%20by%20typing%20%3D'tab1'!E14%20in%20the%20date%20column%20on%20the%20master%20sheet%20then%20dragging%20it%20down%20to%20populate%20all%20the%20rows%20I%20need%20for%20example.%3C%2FP%3E%3CP%3EThe%20purpose%20of%20the%20individual%20tabs%20is%20so%20I%20can%20quickly%20update%20the%20information%20on%20each%20tab%20when%20I%20receive%20it%20(the%20master%20tab%20would%20have%20over%2030%20columns%20so%20its%20easier%20for%20me%20to%20see%20each%20section%20individually)%2C%20however%20my%20boss%20wants%20a%20master%20tab%20where%20she%20can%20see%20all%20information.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20I%20have%20is%20if%20for%20instance%20I%20sort%20the%20flights%20tab%20by%20arrival%20date%20(I%20have%20grouped%20the%20columns%20so%20the%20names%20stay%20correct)%2C%20the%20Master%20sheet%20ends%20up%20with%20the%20dates%20all%20filtered%20too%20but%20the%20names%20are%20in%20the%20wrong%20order.%26nbsp%3B%3C%2FP%3E%3CP%3Eheres%20an%20image%20of%20what%20happens%20so%20hopefully%20explains%20it%20if%20I%20am%20not%20making%20sense!%20hopefully%20its%20clear%20enough%20to%20see.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rebecca0445_0-1643642248576.png%22%20style%3D%22width%3A%20539px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343923i65E88945221A4DA9%2Fimage-dimensions%2F539x408%3Fv%3Dv2%22%20width%3D%22539%22%20height%3D%22408%22%20role%3D%22button%22%20title%3D%22Rebecca0445_0-1643642248576.png%22%20alt%3D%22Rebecca0445_0-1643642248576.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20of%20stopping%20this%20so%20the%20master%20sheet%20stays%20with%20the%20way%20I%20originally%20needed%20it%20when%20the%20other%20tabs%20are%20filtered%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20I%20have%20to%20create%20the%20master%20sheet%20a%20different%20way%20with%20a%20formula%20or%20something%3F%20I%20just%20don't%20know%20how%20or%20where%20to%20find%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3093045%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Hi All,

I've had some great help from here in the past, so hoping you might be able to help again!

I am trying to create a staff plan document that avoids me having to have multiple excel files for the same information.

 

I have created a excel file with multiple sheets ie Tab 1, Flights (has flight details and incoming and out going dates, procurement etc), Tab 2, contact details, Tab 3 Other info like visa status etc.

 

I don't know if this is the correct way make a master sheet but I have populated it by typing ='tab1'!E14 in the date column on the master sheet then dragging it down to populate all the rows I need for example.

The purpose of the individual tabs is so I can quickly update the information on each tab when I receive it (the master tab would have over 30 columns so its easier for me to see each section individually), however my boss wants a master tab where she can see all information. 

 

The issue I have is if for instance I sort the flights tab by arrival date (I have grouped the columns so the names stay correct), the Master sheet ends up with the dates all filtered too but the names are in the wrong order. 

heres an image of what happens so hopefully explains it if I am not making sense! hopefully its clear enough to see. 

Rebecca0445_0-1643642248576.png

 

Is there a way of stopping this so the master sheet stays with the way I originally needed it when the other tabs are filtered?

 

Maybe I have to create the master sheet a different way with a formula or something? I just don't know how or where to find it?

 

Thank you in advance! 

0 Replies