timeline in line chart

  • Thread starter Thread starter Nmeyer
  • Start date Start date
N

Nmeyer

I have a line chart that I need to show a rolling timeline. I currently show
planned against actuals from the project beginning and they just want to see
from today forward. How do I set that up
 
Hi,

One approach is to use the AutoFilter. Select your data and choose Data,
Filter, AutoFilter.
Then open the filter on the date column and choose Custom, Greater than or
equal to, open the list on the right and choose the current date. You chart
will plot from the current date forward.

Alternatively you can use a dynamic range name:
1. Plot the chart for the entire range. For this example my data starts in
A11 with titles on row 10. Column A is dates, and B the data.
2. Choose the Insert, Name, Define and enter the name X
on the refers to line type the following formula:
=OFFSET(Sheet1!$A$10,MATCH(TODAY(),Sheet1!$A$11:$A$51,1),0,COUNT(Sheet1!$A$11:$A$51)-MATCH(TODAY(),Sheet1!$A$11:$A$51,1))
Note the data goes down to row 51 in my example.
3. Select the series on the chart and edit the formula bar to read:
=SERIES(Sheet1!$B$10,trackchanges.xls!X,Sheet1!$B$11:$B$51,1)
trackchanges.xls is the workbooks name and the data is on sheet1.

Cheers,
Shane
 
Back
Top