(Solved) Expanding Chart Data Error

[CraigS26]

My "logical" attempt to Add a Data Year 2024 based on Edit Data of current Chart years produces :
Reference Not valid. Must be to an open worksheet"(see Chart area in link below).

Per Imgur Link below .... I first moved the orange Months Column FROM column P to U and placed Year 24 in P .....
Rt Clk in Chart Area/ Select Data Source/ EDIT the Year 2019 shows: Series name ="2019" --- Series values: ='19-23'!$A$55:$A$66
So, I clk'd ADD/ Series Name ="2024" / Series values: ='19-24'!$P$55:$P$66 .... Nets Error.

WHY does my infallible logic not work? Thanks as always!!

https://imgur.com/QMrBZI1

[HansV]

Why do you use two different sheet names?

[CraigS26]

Other than Series Name I see in the setup box, What 2 Sheet Names are you referring to?

[HansV]

The 2019 series refers to a range on the sheet named 19-23.
The 2024 series refers to a range on the sheet named 19-24.

[CraigS26]

I Added to the 19-23 Chart with Year 2024 so Now it reads 19-24.
Isn't That the way it works?
Any Year from 19-23 shows the Same Values with 19-23 showing BUT different Column Letters (A,D,G, J, M, Now P); Reasoned Adding 24 requires Mod to 19-24.

https://imgur.com/DjmwjtU

[HansV]

But is 19-24 really what you see in the sheet tab at the bottom?

[CraigS26]

https://imgur.com/WK3e6hy


I JUST RE-Named the Tab Sheet 19-24 and IT WORKED. CANNOT believe a Sheet Tab Label Controls so much - Thought it was JUST a Name for a Tab and would have done that LAST. YOU have a lifetime job with all these picky details!! MANY THANKS!!!


Per Imgur just above.... My Very Bottom "Tab Name" for these Years is 19-23 ..... AND I SEE that they were originally SHEET #s;
2 Prior Sheet tabs were 03-13 and 14-18.
My Data Columns are Named 19, 20, 21,22, 23 above the Months Data.

AND, in Rt Clk Select Data IF I Edit the AXIS Label range for (ie) 2019 I SEE THAT the Value Does NOT Reflect 2024..... ='19-23'!$U$55:$U$66
SEE Axis Label Range BOX at TOP RIGHT of Page Data
IF I Mod That to 19-24 I get the same Ref Not valid error.

View this Big Screen and ask any needed follow-up. Thanks!

[HansV]

Your sheet is named 19-23, so you should use ='19-23'!$P$55:$P$66

[HansV]

When you renamed the sheet form 19-23 to 19-24, Excel automatically updated the definition of the x-axis labels and of the y-values of the earlier series to refer to 19-24 instead of 19-23. And using 19-24 for the new series worked too.

Moral of the story: in a formula such as ='19-23'!$U$55:$U$66, 19-23 is the name of the sheet containing the range U55:U66.

[CraigS26]

Your sheet is named 19-23, so you should use ='19-23'!$P$55:$P$66

Did you miss my last post (ie) Solved .....In my Post two above your last quoted here I showed via Imgur THAT my Tab Sheet was STILL Named 19-23.
WHEN I Renamed it --- 19-24 --- Everything Worked as originally attempted (ie) ='19-24'!$P$55:$P$66
I can only repeat: I Thought That Tab name 19-23 was JUST a NAME and can't believe it was EVERYTHING to this attempt.
Thanks again!!!

https://imgur.com/YakF3y3

[HansV]

You edited your previous reply while I was posting mine...