Sheet Reference Issue

%3CLINGO-SUB%20id%3D%22lingo-sub-2210288%22%20slang%3D%22en-US%22%3ESheet%20Reference%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2210288%22%20slang%3D%22en-US%22%3E%3CP%3EI%20often%20construct%20formulas%20by%20typing%20%22%3Dfunction...%22%20and%20selecting%20the%20cell%20ranges%20by%20mouse%20or%20cursor.%3C%2FP%3E%3CP%3EWhen%20a%20formula%20refers%20to%20a%20cell%20on%20the%20same%20sheet%2C%20the%20sheet%20name%20is%20not%20needed%20in%20the%20reference%20and%20Excel%20does%20not%20include%20it.%20References%20to%20cells%20on%20other%20sheets%20require%20the%20sheet%20name%20and%20Excel%20inserts%20it.%3C%2FP%3E%3CP%3EHowever%2C%20Excel%20%3CSTRONG%3Edoes%3C%2FSTRONG%3E%20insert%20the%20sheet%20name%20for%20references%20to%20cells%20on%20the%20same%20sheet%20as%20the%20formula%2C%20if%20another%20sheet%20has%20been%20referred%20to%20earlier%20in%20the%20formula.%3C%2FP%3E%3CP%3EAnyone%20know%20why%20it%20behaves%20this%20way%3F%20It%20causes%20errors%20if%20the%20formula%20is%20later%20sorted.%3C%2FP%3E%3CP%3EI%20have%20trained%20myself%20to%20manually%20remove%20the%20unwanted%20sheet%20references%20but%20is%20there%20a%20better%20way%3F%3C%2FP%3E%3CP%3EPlease%20see%20the%20attached%20file.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2210288%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-2210464%22%20slang%3D%22en-US%22%3ERe%3A%20Sheet%20Reference%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2210464%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F997263%22%20target%3D%22_blank%22%3E%40okkoala%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20has%20always%20behaved%20this%20way%20-%20once%20you%20switch%20to%20another%20sheet%20while%20constructing%20a%20formula%2C%20it%20will%20add%20the%20sheet%20name%20to%20all%20cell%20references.%20I%20assume%20that%20the%20developers%20set%20a%20flag%20to%20True%20when%20you%20switch%20to%20another%20sheet%2C%20and%20don't%20bother%20to%20reset%20it%20when%20you%20switch%20back%20to%20the%20sheet%20with%20the%20formula.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlthough%20I%20find%20it%20mildly%20annoying%2C%20I%20have%20never%20experienced%20problems%20caused%20by%20this...%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I often construct formulas by typing "=function..." and selecting the cell ranges by mouse or cursor.

When a formula refers to a cell on the same sheet, the sheet name is not needed in the reference and Excel does not include it. References to cells on other sheets require the sheet name and Excel inserts it.

However, Excel does insert the sheet name for references to cells on the same sheet as the formula, if another sheet has been referred to earlier in the formula.

Anyone know why it behaves this way? It causes errors if the formula is later sorted.

I have trained myself to manually remove the unwanted sheet references but is there a better way?

Please see the attached file.

Thanks

 

Excel 2016 on Windows 10

2 Replies

@okkoala 

Excel has always behaved this way - once you switch to another sheet while constructing a formula, it will add the sheet name to all cell references. I assume that the developers set a flag to True when you switch to another sheet, and don't bother to reset it when you switch back to the sheet with the formula.

 

Although I find it mildly annoying, I have never experienced problems caused by this...

IMHO it's clearly a bug in Excel which can lead to severe consequences. Like OP I also trained myself to clean it up.

I clean it up since long ago I sorted value I was retrieving from another sheet and suddenly all values where were wrong. I didn't detect it because my total value was still good. It's only later working with a co-worker that we discovered it and all I did was wrong because of it. (It was calculation for entities and all were accounted in wrong entities...)