Excel Formulas Messing Up References When Sorting Referenced Sheet

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


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


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