Excel Formulas Messing Up References When Sorting Referenced Sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-2661795%22%20slang%3D%22en-US%22%3EExcel%20Formulas%20Messing%20Up%20References%20When%20Sorting%20Referenced%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2661795%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20I%20am%20trying%20to%20do%20a%20mail%20merge%20on%20where%20email%20people%20the%20month%20of%20their%20meeting.%20I%20have%20created%20tabs%20for%20each%20month%20(1-12)%20which%20have%20reference%20formulas%20to%20the%20main%20sheet%20Titled%20%22All%20Corps%22.%26nbsp%3B%20%26nbsp%3BIf%20the%20number%20in%20column%20C%20is%201%20then%20it%20should%20go%20in%20sheet%20%22Jan%22%20and%20so%20forth.%26nbsp%3B%20If%20any%20fields%20are%20changed%20on%20the%20main%20All%20Corps%20Screen%20they%20need%20to%20also%20change%20on%20the%20monthly%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20sort%20%22All%20Corps%22%20A%20to%20Z%20but%20if%20I%20do%20the%20references%20on%20the%20different%20month%20sheets%20get%20screwed%20up.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20make%20A-Z%20formatting%20on%20%22All%20Corps%22%20and%20have%20still%20have%20monthly%20sheets%20sorted%20A-Z%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20note%20that%20the%20%22All%20Corps%22%20list%20is%20not%20static%20and%20there%20will%20need%20to%20be%20new%20people%20added%20to%20the%20lists%20and%20sorted%20in%20A-Z.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2661795%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2662522%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%20Messing%20Up%20References%20When%20Sorting%20Referenced%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2662522%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1131605%22%20target%3D%22_blank%22%3E%40Berkley_B%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EInstead%20of%20references%20please%20use%20XLOOKUP()%20or%20similar%20formula%2C%20depends%20on%20which%20version%20of%20Excel%20you%20are.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20531px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F304204i0F6E386810D282F0%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EWith%20that%20sorting%20won't%20affect%20data%20in%20this%20sheet.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have a spreadsheet that I am trying to do a mail merge on where email people the month of their meeting. I have created tabs for each month (1-12) which have reference formulas to the main sheet Titled "All Corps".   If the number in column C is 1 then it should go in sheet "Jan" and so forth.  If any fields are changed on the main All Corps Screen they need to also change on the monthly sheet.

 

I want to sort "All Corps" A to Z but if I do the references on the different month sheets get screwed up.

 

How can I make A-Z formatting on "All Corps" and have still have monthly sheets sorted A-Z?

 

Please note that the "All Corps" list is not static and there will need to be new people added to the lists and sorted in A-Z.

 

Thank you for your help! 

1 Reply

@Berkley_B 

Instead of references please use XLOOKUP() or similar formula, depends on which version of Excel you are.

image.png

With that sorting won't affect data in this sheet.