Sheet Reference Issue

[okkoala]

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]

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

[#3]

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

[#4]

I was able to solve this last year by changing a setting in Google Drive File Stream, which is installed on my computer. Previously the option 'Show who is editing Office files, and share with other editors if I am editing' was checked. After unchecking this, it seems like this fixed the issue.

Since then, the google application has changed and the option looks different. But might be worth checking if it's a file-sync interaction across google drive / box / dropbox / onedrive / etc by disabling them and see if it fixes the issue.

I agree, it is super annoying that excel references the active sheet as if it were an external sheet. It makes the data unsortable and prone to errors.