Dynamic Chart Error

[Native]

Hello,

I've read the dynamic column chart example on http://peltiertech.com/Excel/Charts/Dynamics.html
but keep running into the same error:

"A formula in this worksheet contains one or more invalid references.
Verify that your formulas contain a valid path, workbook, range....etc"

This error appears when I try to change the Series formula to the
range names ChartValues and ChartLabels.

I've gone to the length of even copying formulas for the range names
from the Peltiertech example and still no luck. I've checked my range
names just in a formula. For example, I've typed "=ChartValues" and
when I open (F2) and hit F9, I see the array of data I want. Same with
"=ChartLabels" the array I want with labels. Both have the same number
of items so it's not like one has 15 and the other 16.

Any suggestions as to what I am missing???

Below are my ranges:

ChartLabels
=OFFSET(Sheet1!ChartValues,0,-1)
={"P1";"P2";"P3";"P4";"P5";"P6";"P7";"P8";"P9";"P10";"P11";"P12";"P13";"P14";"P15"}

ChartValues
=OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)
={25;26;23;22;19;26;27;25;28;31;30;45;24;28;29}

 

[Native]

A bit more info....

Here is the formula I tried to enter into the Series:

=SERIES(Sheet1!$B$1,ChartLabels,ChartValues,1)

Also, interestingly, when I tried to add the Sheet1 reference, when I
hit the Enter key nothing would happen:

=SERIES(Sheet1!$B$1,Sheet1!ChartLabels,Sheet1!ChartValues,1) and then
pressed [Enter] nothing happens.

 

[John Mansfield]

I believe your problem might be in your sheet reference. This is starting
from the beginning so bear with me and step through the following:

First, create an empty workbook. Save it with the file name "Test.xls".

Add the following data to Sheet1 - Range A1:B16:

Labels Data
P1 25
P2 26
P3 23
P4 22
P5 19
P6 26
P7 27
P8 25
P9 28
P10 31
P11 30
P12 45
P13 24
P14 28
P15 29

Note that the word "Labels" should appear in cell A1 and the word "Data" in
cell B1. The actual X-Axis labels are placed in the range A2:A16 and the
data is placed in the range B2:B16.

Go to Insert -> Name -> Define. You should be in the named range dialog box.

First, enter the following formula in the named range dialog box and name it
"ChartValues".

=OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)

Second, enter the following formula in the named range dialog box and name
it "ChartLabels".

=OFFSET(Test.xls!ChartValues,0,-1)

You should now have two named ranges in the dialog box . . . "ChartLabels"
and "ChartValues". Exit the named range dialog box.

Now, click once on your embedded chart to activate it.

Go to Chart -> Source Data -> Series Tab

In the values input for your first series, enter the formula:

=Test.xls!ChartValues

In the Category (X) axis labels input, enter the formula:

=Test.xls!ChartLabels

At this point your chart should automatically update as you add more data.

 

[Native]

John!!!! Bingo! that was it! I needed to put the workbook reference in
the Series function.

eg - =Test.xls!ChartValues vs. simply =ChartValues