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

@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.