Cell reference different sheet changes when referenced sheet is sorted

%3CLINGO-SUB%20id%3D%22lingo-sub-3019869%22%20slang%3D%22en-US%22%3ECell%20reference%20different%20sheet%20changes%20when%20referenced%20sheet%20is%20sorted%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3019869%22%20slang%3D%22en-US%22%3EI%20have%20a%20workbook%20with%20multiple%20sheets.%20One%20of%20my%20sheets%20is%20a%22summary%22%20sheet.%20It%20references%20cells%20in%20the%20%22master%22%20sheets.%20Sometimes%20I%20want%20the%20master%20sheets%20sorted%20by%20the%20date%20column%2C%20sometimes%20I%20sort%20them%20by%20the%20customer%2C%20etc.%20When%20I%20sort%20the%20master%20pages%2C%20the%20summary%20page%20stays%20referenced%20to%20the%20absolute%20cell%20it%20originally%20referenced%20and%20does%20not%20follow%20the%20intended%20cell%20that%20moved%20when%20it%20was%20sorted.%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20a%20way%20to%20make%20the%20summary%20page%20references%20follow%20the%20cells%20on%20the%20other%20pages%20as%20they%20move%20when%20they%20are%20sorted%20in%20different%20ways%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20in%20advance.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3019869%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3019923%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20reference%20different%20sheet%20changes%20when%20referenced%20sheet%20is%20sorted%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3019923%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1233997%22%20target%3D%22_blank%22%3E%40JasonMD%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20add%20some%20ID%20to%20data%20in%20master%20sheets%20and%20use%20any%20of%20lookup%20functions%20(XLOOKUP%2C%20etc)%20to%20collect%20data%20in%20summary%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor
I have a workbook with multiple sheets. One of my sheets is a"summary" sheet. It references cells in the "master" sheets. Sometimes I want the master sheets sorted by the date column, sometimes I sort them by the customer, etc. When I sort the master pages, the summary page stays referenced to the absolute cell it originally referenced and does not follow the intended cell that moved when it was sorted.

Is there a way to make the summary page references follow the cells on the other pages as they move when they are sorted in different ways?

Thank you in advance.
1 Reply

@JasonMD 

That's add some ID to data in master sheets and use any of lookup functions (XLOOKUP, etc) to collect data in summary sheet.