Filtering Excel 2003 Charts to a date range

[tpeter]

I have multiple charts that we look for trends on over 1-2 year time frames.
Is it possible to have a chart show all the data that has been entered and
when looking at the chart have a date range data to format that range
differently. What I am trying to do is we have weekly or montly meetings, in
these meetings I would like to show all the additional data differently that
has been added since our last meeting, while showing accumulative data in a
different format.

 

[Jon Peltier]

The easiest way to get different formats is to use different series. If you
had one series, you could use autofiltering to narrow the displayed range of
data. However,I would probably build something with defined names to help
split the display into different series.

Suppose you have dates in column A (A2:A30) and values in column B (B2:B30).
Name the range of dates AllDates and the range of values AllValues. To do
this, select the range, and type the name into the Name Box (above cell A1).
Put the cut-off date into a cell (E3) and name this cell CutOffDate. Define
some dynamic ranges of values: Go to Insert menu > names > Define. Define
these two names:

Name: OldValues
Refers To:
=IF(AllDates<=CutOffDate,AllValues,NA())

Name: NewValues
Refers To:
=IF(AllDates>=CutOffDate,AllValues,NA())

This yields ranges with either the values or with unplottable error values
#N/A. I used both <= and >=, so both series include a point at CutOffDate.
Adjust the inequality operator to get what you need.

Create an XY chart using AllDates and AllValues. The series formula looks
like:

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$30,Sheet1!$B$2:$B$30,1)

You could change it to

=SERIES(Sheet1!$B$1,Sheet1!AllDates,Sheet1!AllValues,1)

and Excel will change it to

=SERIES(Sheet1!$B$1,Book1.xls!AllDates,Book1.xls!AllValues,1)

but the series will remain in the chart. Change this to

=SERIES(Sheet1!$B$1,Book1.xls!AllDates,Book1.xls!OldValues,1)

and only the values before CutOffDate will appear. Copy the series formula,
select the plot area or chart area of the chart, click in the formula bar,
and paste the formula, then edit it to

=SERIES(Sheet1!$B$1,Book1.xls!AllDates,Book1.xls!NewValues,2)

A new series appears which shows only the new values. If you adjust
CutOffDate, the transition between old and new values in the chart moves
automatically.

- Jon

 

[Jon Peltier]

That wasn't the easiest way, on second thought, though it's among the most
flexible. The following way is somewhat easier, and the values are all in
the sheet, so it's easier to figure out where you may have messed up.

As before, assume your dates are in A2:A30 and your values are in B2:B30
with titles in A1 and A2. Put your cutoff date in F3. Enter 'Old Dates' in
C1 and 'New Dates' in D1, and enter the following formulas

C2
=IF(A2<=$F$3,B2,NA())

D2
=IF(A2>=$F$3,B2,NA())

and fill these down to row 30. Select A1:A30, then hold CTRL while selecting
C130. Run the chart wizard and create an XY chart. The first series
(column C) ends at the cutoff date, and the second series (column D begins
there).

- Jon

 

[tpeter]

Jon,

Just wanted to say thank you for your help and quick response. I have the
boss looking at it now but it looks like with a few tweeks this will work.
Thank you again for your help.

Tim