Forum Discussion
Reference error when using local names
Indeed odd. But IMHO using a range name local to Sheet2 for a chart on Sheet1 is very unusual and unexpected.
- Sa12Aug 21, 2025Copper Contributor
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.- JKPieterseAug 22, 2025Silver Contributor
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.
- biance098Aug 22, 2025Copper Contributor
great
- SergeiBaklanAug 22, 2025Diamond Contributor
IMHO, it worked and works if we use references on ranges, not on named array constants. With the latest error on recalculation appears even if we use Workbook scope.
- Sa12Aug 22, 2025Copper Contributor
That makes the bug worse at least for me, I'll have to test it, thank you.