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

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
--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Belinda" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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

--
Regards,
Tom Ogilvy

"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Belinda" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
>
>

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

Don Guillett wrote:

> 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