Forum Discussion
Reference error when using local names
I'm pretty certain the behavior was not like this before, but not sure at which version it changed.
Why is it unusual? It's within its intended use as far as I know.
I've been using it for a while, an example scenario is when you have same name in different sheets, for example Sheet1!Values, Sheet2!Values, Sheet3!Values, it makes it easier to copy the same formula to different sheet without having to change the references, because =Values is implicitly referring to the current sheet, and then you might want to use a chart to visually compare the different values of each sheet.
If every sheet was for a match in a game, you can do match1!Winners, match2!Winners,
local names make it easier to know what are you referring to in a formula or avoid intellisense if the name shouldn't be relevant outside its sheet.
I agree local names are useful, but I find it unusual that a name local to sheet1 is used in formulas on sheet2. Perhaps not if you're totaling different sheets. But then I'd argue having data spread across sheets isn't the best design for your data :-)
- Sa12Aug 24, 2025Copper Contributor
I agree but that assumes the data setup design is the priority, data model and other routes are more suitable in such cases. I'd argue if we're using excel then data design is usually not the main or only priority, it almost always includes user accessibility and convenience, or else it would be a bit abstract through databases in comparison to excel.
My priority is workbook automation and user accessibility & convenience, local names are very useful in my cases, your Name Manager has been a gamechanger by the way, thank you! FlexFind is very neat too :-)
I hope someone has reported this bug by now.