I have a bar graph that shows revenue by week, for week 1 thru week
52. Each week the graph pulls in the current week results. Is there
a way to have the graph only show results for week 1 thru the current
week, without me having to adjust the data source range each week.

Also, I have a bar graph that shows year-to-date revenue that is
updated each week. Is there a way that the scale can be automatically
updated to fit the data, without me having to manually adjust each
week.

1. Use dynamic ranges to populate the chart series:

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

2. Set the axis scale parameters to Auto (double click the axis, click the
Scale tab, check all the Auto boxes).

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


"glenlee" wrote in message
...
I have a bar graph that shows revenue by week, for week 1 thru week
52. Each week the graph pulls in the current week results. Is there
a way to have the graph only show results for week 1 thru the current
week, without me having to adjust the data source range each week.

Also, I have a bar graph that shows year-to-date revenue that is
updated each week. Is there a way that the scale can be automatically
updated to fit the data, without me having to manually adjust each
week.

Thanks for the input. Dynamic Ranges work great when the source data
cells contain values. But I can't get it to work when the source data
cells contain formulas that equal -0-. Those weeks appear in my graph
with no results. I think using the Counta function, counts those
weeks even though there is no data for them.

On Thu, 5 Apr 2007 07:56:35 -0400, "Jon Peltier"
wrote:

1. Use dynamic ranges to populate the chart series:

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

2. Set the axis scale parameters to Auto (double click the axis, click the
Scale tab, check all the Auto boxes).

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


"glenlee" wrote in message
...
I have a bar graph that shows revenue by week, for week 1 thru week
52. Each week the graph pulls in the current week results. Is there
a way to have the graph only show results for week 1 thru the current
week, without me having to adjust the data source range each week.

Also, I have a bar graph that shows year-to-date revenue that is
updated each week. Is there a way that the scale can be automatically
updated to fit the data, without me having to manually adjust each
week.

COUNTA counts non-blank cells; note that a cell with a formula that returns
"" is not blank, because it contains a formula. COUNT counts cells with
numerical values.

If you use formulas that return zero when a linked cell is blank, you should
change the formula to return something else:

=IF(LEN(A1)0,A1,"")
looks nice, looks like a blank, plotted as zero

=IF(LEN(A1)0,A1,"blank")
descriptive, plotted as zero

=IF(LEN(A1)0,A1,NA())
ugly #N/A error (hide with conditional formatting), not plotted in a line or
XY chart

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


"glenlee" wrote in message
...
Thanks for the input. Dynamic Ranges work great when the source data
cells contain values. But I can't get it to work when the source data
cells contain formulas that equal -0-. Those weeks appear in my graph
with no results. I think using the Counta function, counts those
weeks even though there is no data for them.

On Thu, 5 Apr 2007 07:56:35 -0400, "Jon Peltier"
wrote:

1. Use dynamic ranges to populate the chart series:

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

2. Set the axis scale parameters to Auto (double click the axis, click the
Scale tab, check all the Auto boxes).

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


"glenlee" wrote in message
...
I have a bar graph that shows revenue by week, for week 1 thru week
52. Each week the graph pulls in the current week results. Is there
a way to have the graph only show results for week 1 thru the current
week, without me having to adjust the data source range each week.

Also, I have a bar graph that shows year-to-date revenue that is
updated each week. Is there a way that the scale can be automatically
updated to fit the data, without me having to manually adjust each
week.