Using formulas for graph series

Question

Sample Data

X   Y
-10 100
-5  25
-2.5    6.25
0   0
2.5 6.25
5   25
10  100

So I have the above sample data sitting in the range C4:D10

In A5 I place the number of data points (rows) I wish to include in my graph

Since A5 can change I come up with two formulas that will give me a dynamic range.

X-Values
=Sheet1!$C$4:INDEX(Sheet1!$C:$C,3+Sheet1!$A$5)
Y-Values
=Sheet1!$D$4:INDEX(Sheet1!$D:$D,3+Sheet1!$A$5)

and

X-Values
=OFFSET($C$4,0,0,$A$5,1)
Y-Values
=OFFSET($D$4,0,0,$A$5,1)

I also put the first formula into a named range

=Xseries1
=Yseries1

When I try to enter the formulas or named ranges in the area for the X and Y values in an X-Y Scatter plot, I get an error.

Doing some research, I stumbled across an entry somewhere that said something about placing the name of the workbook out front. This worked for the named ranges.

However when I tried to do it with just the formulas, I got the error again.

=[Book1]Sheet1!$C$4:INDEX([Book1]Sheet1!$C:$C,3+[Book1]Sheet1!$A$5)

Is there a way to do this without named formulas?

Answer 1

No, there is no way without named formulas.

You cannot put a formula in the XY chart source dialog. It only accepts ranges or range names. As you found out, you can't enter just a range name, you must precede it with the workbook name or a sheet name.

Since my workbook names and sheet names can be quite long, I often create a sheet with a one-letter name, just so I can use it in the dialog and avoid all that typing. If the range name has a workbook scope, you can use any sheet name in the chart dialog.

The range name approach can be quite powerful and it makes a lot of sense to allow range names, but not formulas. If you need to change the formula, you only need to change it once in the range name definition, not many times in each chart series formula.