Dynamic Stacked Column Chart Help

K

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
=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

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

KSP

J

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

K

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