Dynamic Stacked Column Chart Help

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/...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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"ksp" <[email protected]> wrote in message
news:[email protected]...
>
> 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/...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
>
>
> --
> ksp
> ------------------------------------------------------------------------
> ksp's Profile:
> http://www.excelforum.com/member.php...fo&userid=6267
> View this thread: http://www.excelforum.com/showthread...hreadid=552170
>

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