Dynamic Stacked Column Chart Help

[ksp]

I am trying to create a dynamic stacked column chart for some product
based sales information. I have used some information that I found at

http://peltiertech.com/Excel/Charts/Dynamics.html#DynColCht

to create dynamic named ranges for my data with the following formulas
that I found for a dynamic column chart
ChartData
=OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)
ChartLabels
OFFSET(ChartValues,0,-1)

However my chart does not appear to be graphing all of the data so I
suspect there is something wrong with how I am setting it up or that
these formulas don;t liek the fact that I am trying to do a stacked
column chart.

My data looks like this

National Data
Product Total $ Area A $ Area B $ Area C $
m
n
n
p
x
y
z


State A Data
Product Total $ Area A $ Area B $ Area C $
m
n
n
p

State B Data
Product Total $ Area A $ Area B $ Area C $
n
x
y
z

There can be up to approximately 50 products so I am leaving rows in
the grid for the user to enter the additional products, and the stacked
column needs to be of the 3 area’s to represent the total sales.

At the moment I have only tried to graph the national data where Area A
is in Column B, and the first product listed is in Row 7 (the data grid
goes down to Row 61), using the following formula for the data called
AreaAData
=OFFSET('Sheet1'!$C$6,1,0,COUNTA('Sheet1'!$C$7:$C$61)-1,1)
etc for all the Area's
And this for the labels
=OFFSET(Sheet1!AreaAData,0,-2)

Parts of some of the series are being graphed and then inother
instances it is being missed but I have no idea why.

Doea anyone have any ideas or any websites that have info about dynamic
stacked charts as I haven't found anything much in my searches so far

Thanks in advance

KSP

 

[Jon Peltier]

The series in a stacked chart must have exactly the same X values. Your
State A and State B data will not line up properly.

You could probably make this easier if you set up the data as input for a
pivot table. one column for state, one for area, one for product, one for
dollars. You'll get all the output nicely aligned, with blanks where
appropriate.

- Jon

 

[ksp]

Thanks Jon

I had been trying to solve this for the last few days and got it to
work yesterday - this was one problem that I fixed the other was that I
had blank cells for zero values instead of a zero which was causing
problems

Also wanted to say that your website has been a huge help to me with
this project - thanks

KSP