I've learnt a lot from the articles on dynamic chart ranges using named
ranges and offsets. My problem is a little different, in 2 parts:
a) My spreadsheet shows data for a month, with a line for each day. I want
the graph to always show dates 1-31, (IE X values constant) and display 3
ranges of data for each day. Each day's data is automatically filled by lnks
to other spreadsheets received daily from external suppliers, but the cells
contain formulas, which return a "" or 0 if no data is present for any day.
Theses are shown as zeros on the graph. The counta still shows the cells as
zeros because of the presence of the formulas as they are not empty. As I
dont have a dynamic X value named range, Im not up to defining a dynamic Y
value range - please can someione advise?
b) second problem is that I also show a trendline based on data MTD, and in
options project forward to the end of the month. I manually cahnge this every
day - can I alter the formula for the trendline option to extend forwards by
the relevant number of days needed to project to the end of the month?
Please keep the answer simple - Im not an advanced user, just a keen
learner. Many thanks

1. Change the formulas so they return NA() instead of 0 or "" for a blank
2. Calculate the trendline parameters in the worksheet using SLOPE() and
INTERCEPT(), and draw your line as a new XY series from X=0 to X=31.

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


"Mi Manager" <Mi (E-Mail Removed)> wrote in message
news:B3FEC7CC-24DF-43E8-AF67-(E-Mail Removed)...
> I've learnt a lot from the articles on dynamic chart ranges using named
> ranges and offsets. My problem is a little different, in 2 parts:
> a) My spreadsheet shows data for a month, with a line for each day. I want
> the graph to always show dates 1-31, (IE X values constant) and display 3
> ranges of data for each day. Each day's data is automatically filled by
> lnks
> to other spreadsheets received daily from external suppliers, but the
> cells
> contain formulas, which return a "" or 0 if no data is present for any
> day.
> Theses are shown as zeros on the graph. The counta still shows the cells
> as
> zeros because of the presence of the formulas as they are not empty. As I
> dont have a dynamic X value named range, Im not up to defining a dynamic Y
> value range - please can someione advise?
> b) second problem is that I also show a trendline based on data MTD, and
> in
> options project forward to the end of the month. I manually cahnge this
> every
> day - can I alter the formula for the trendline option to extend forwards
> by
> the relevant number of days needed to project to the end of the month?
> Please keep the answer simple - Im not an advanced user, just a keen
> learner. Many thanks

Thank you so much Jon- solution turned out to be simple in the end - but I
have learnt a lot along the way. Thank you for sharing your knowledge - very
grateful.

"Jon Peltier" wrote:

> 1. Change the formulas so they return NA() instead of 0 or "" for a blank
> 2. Calculate the trendline parameters in the worksheet using SLOPE() and
> INTERCEPT(), and draw your line as a new XY series from X=0 to X=31.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Mi Manager" <Mi (E-Mail Removed)> wrote in message
> news:B3FEC7CC-24DF-43E8-AF67-(E-Mail Removed)...
> > I've learnt a lot from the articles on dynamic chart ranges using named
> > ranges and offsets. My problem is a little different, in 2 parts:
> > a) My spreadsheet shows data for a month, with a line for each day. I want
> > the graph to always show dates 1-31, (IE X values constant) and display 3
> > ranges of data for each day. Each day's data is automatically filled by
> > lnks
> > to other spreadsheets received daily from external suppliers, but the
> > cells
> > contain formulas, which return a "" or 0 if no data is present for any
> > day.
> > Theses are shown as zeros on the graph. The counta still shows the cells
> > as
> > zeros because of the presence of the formulas as they are not empty. As I
> > dont have a dynamic X value named range, Im not up to defining a dynamic Y
> > value range - please can someione advise?
> > b) second problem is that I also show a trendline based on data MTD, and
> > in
> > options project forward to the end of the month. I manually cahnge this
> > every
> > day - can I alter the formula for the trendline option to extend forwards
> > by
> > the relevant number of days needed to project to the end of the month?
> > Please keep the answer simple - Im not an advanced user, just a keen
> > learner. Many thanks
>
>
>