Dynamic Range of Scatter Graph - error when file gets copied.

[weeblesue]

I have scoured the forum for tips on Dynamic Ranges of graphs, in particular a scatter graph. I can't find a solution to the problem that is happening for me. When a user receives a copy of my spreadsheet, the name gets changed. But because the name gets changed, the named ranges that I am using for my dynamic ranges of the graph portion are reporting the old lovely "A formula in this worksheet contains one or more invalid references (etc.)." By process of elimination, i know it is the graph ranges that cause this.

Here's the sort of data that gets graphed:
Col1 Col2 Col3
60 200 150
50 190 143
40 180 134
30 170 126
20 160 119


The issue is that the overall table is a fixed length of 12 entries, but the entries can vary anywhere from 5 rows to 12. This is why the range has to be variable.
In my xy graph, i want the data VALUES - not the row counter - as the x-axis. So the x-axis should have the labels of 100, 120, 130 etc. NOT 1, 2, 3, 4, 5.

In order to do this, the range MUST BE only the amount of data that is populated. If i select the column of data to be all 12 rows, then I end up with 1,2,3,....10,11,12 as the x-axis labels.

NamedRange1 = =OFFSET('HC1'!$B$14,0,0,COUNT('HC1'!$B$14:$B$26),1)
NamedRange2 = =OFFSET('HC1'!$D$14,0,0,COUNT('HC1'!$D$14:$D$26),1)
NamedRange3 = =OFFSET('HC1'!$C$14,0,0,COUNT('HC1'!$C$14:$C$26),1)

In my graph, the Series1 X values are ='HC1'!NamedRange3
Y values are ='HC1'!NamedRange1.

Series 2 X values are ='HC1'!NamedRange2
Y values are ='HC1'!NamedRange1.

All named ranges are based on the TAB name, not the Worksheet. (Scope = HC1, my tab name)

For some odd reason, when this file gets copied and gets a new name, NamedRange3 goes wonky. The worksheet does not recognize this named range! It is STILL just as defined in the Name Manager. Data is present in the cells. But in the graph, the SeriesX values are now ={"HC1!NamedRange3"}.
If I delete this and enter HC1!NamedRange3 {OK}, then the graph populates correctly.

Both other ranges work just fine, and the file recognizes them. The data populates for that series and the graph just fine.

I don't use "namedrange," I have tried various names for this errant range. EffluentTemp, EffTemp, XYZABC, and so on. No matter what I name it, error. The data source is no different for this range than any others.
I have deleted the graph and recreated. I have deleted the named ranges and recreated.

I correct it in the one file, and it works fine.... until I copy the file and give it a new name. Many people use this file and they have to rename it for their own purpose so yes, it has to be renamed.

Any ideas?
Many thanks,
Susan

 

[GooberTron]

For a scatter plot does it really matter if the range contains blank cells? As in using standard ranges instead of offset / count named ranges. It might be more reliable though I don't have a good explanation as to why your named range is going weird.

 

[weeblesue]

Apparently it matters a lot.

If there are any blank cells at the end of the range, the x-axis reports the number of points. 1,2,3,4,5, etc.
If the range is completely filled, the x-axis contains the values (and this is what i want) - 100, 120, 140, 160, etc.
It's not that there are blank cells in the middle of the range, there are blanks at the end. I only fill in 10 of the 12 rows in the column. Or only 6.

I should include that it's not strictly a scatter plot - lines connect the points. Excel just calls it a "scatter" plot. In the scatter plot selection, it's the 2nd option (Scatter with smooth lines and markers).

So why don't I just use the Line graph type? Because the users need the line to go a certain direction (upward and to the right). If I use a line graph definition, then the line goes downward instead. Just reverse the order, you say? Then the y-axis appears on the right and not the left.
I have very whiny and picky users and they want the graph to look like they want it, so that means using a scatter type.