0

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.

Edit Series

Edit Series 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.

Workbook Edit Series

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)

Workbook Edit Series not working

Is there a way to do this without named formulas?

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.