Graph data series named range challenges - terribly frustrating

[hcgood]

I've developed a spreadsheet used by dozens in my org. It has a graph that uses a named range (GraphPlanned) based on dynamic data on a different worksheet called "Calculations".
But when I enter the graph data series as ='Calculations'!GraphPlanned it accepts it, but then when I click again on Edit to view it, it always changes it to ='!FLD PnP 26Nov test.xlsm'!GraphPlanned

This works fine as long as its on my computer, but if I change the file name and reopen it, then it triggers the dreaded Link alert asking if I want to open the other spreadsheet.
Or if I upload it to the cloud for others to download and use, they encounter this error.

What am I doing wrong? This is really, really frustrating. Any help would be so much appreciated.
Below is the GraphPlanned named range if that's useful.

=OFFSET(INDEX(Calculations!$DE$36:$DE$119,MATCH((Calculations!$CA$30&"1")*1,Calculations!$DA$36:$DA$119)),0,0,(Calculations!$CA$31-Calculations!$CA$30)*4+4)

 

[6StringJazzer]

Excel's treatment of named ranges in chart series is not consistent with how it's used everywhere else. For some reason, it qualifies the named range with the name of the file, as you have seen.

I suggest that instead of changing the name of a closed file using Windows, you open it and save it from Excel with a different name. I am not 100% sure that will solve it but it often does help with naming problems.

 

[hcgood]

Thanks for your input. I too have noticed a difference in those two methods of renaming the file. I'll have to explore this some more and figure out a consistent way to do this and then document it for the other users. Many thanks.