Forum Discussion
Reference error when using local names
When a local reference is used in a chart, then expanding a table by entering a value under it, causes reference error that keeps appearing until the file is reopened.
Version: Microsoft® Excel® for Microsoft 365 MSO (Version 2507 Build 16.0.19029.20136) 64-bit
Steps to reproduce:
1. Make a new xlsx file, add a second sheet.
2. In 2nd sheet create two local references, it can be static values or ranges.
3. In 1st sheet insert a chart, set series x & y values to use the 2nd sheet local references.
4. In 1st sheet insert a new table, write a value under the table to make it expand.
The reference error will appear and will keep appearing for certain actions, including the action of entering a value in cells.
My work account doesn't have permission to report a bug and has feedback disabled due to unnecessary policy, would be grateful if someone can report this bug. You can use the attached gif.
Thanks
7 Replies
Indeed odd. But IMHO using a range name local to Sheet2 for a chart on Sheet1 is very unusual and unexpected.
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 :-)
