How to display a chart where number of periods vary

[Belinda]

Hello All

I have the following Excel report layout:

Value Period Actual YTD Budget YTD Variance YTD
===============================================================================
Net sales 001 xxxx xxx xxx
Net Sales 002 xxxx xxx xxx
Net sales 003 xxxx xxx xxx
Net sales 004 xxxx xxx xxx
Net sales 005 xxxx xxx xxx
Net sales 006 xxxx xxx xxx
COGS 001 xxxx xxx xxx
COGS 002 xxxx xxx xxx
COGS 003 xxxx xxx xxx
COGS 004 xxxx xxx xxx
COGS 005 xxxx xxx xxx
COGS 006 xxxx xxx xxx
Prod Mar 001 xxxx xxx xxx
Prod Mar 002 xxxx xxx xxx
Prod Mar 003 xxxx xxx xxx
Prod Mar 004 xxxx xxx xxx
Prod Mar 005 xxxx xxx xxx
Prod Mar 006 xxxx xxx xxx

Now I want to have one chart to display the Net sales and one more to
display COGS and one more for Product Margin.

But my problem is the number of periods can be 3 periods or 5 periods
or 8 periods or 12 periods. When my range is changing everytime the
user runs a report how do I create a chart with such a variable range
can any of you please throw light how I can implement a chart with a
varying range as above.

Thanks
Belinda

 

[Don Guillett]

insert>name>define
Use a defined name for your chart series such as
netsales
=offset($A$2,0,0,counta($a:$a),1)
now your source would be =sheet1!netsales

 

[Tom Ogilvy]

Right idea, but she would have to dynamically determine the anchor cell and
the count would have to be conditional.

 

[Jon Peltier]

I was thinking Pivot Table, three of them in fact, each based on the
same data. Each PT uses a different Value field in its Page area. Each
PT has a Pivot Chart based on it, or a regular chart that's driven by
defined names, as Don suggests. Change the data sheet, and everything
else updates.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______