Dynamic source data based on Today

[SPISO]

I am creating a production barchart that is updated daily. I want the chart
to plot from 3 days old to 4 days in the future. I would like the middle of
the chart to represent today. Tomorrow's data will become today's data when
everything shifts by one at midnight.

I am pretty new to macro so please speak in laymans terms.

 

[Tushar Mehta]

No need for a macro. Adapt the ideas at
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html
particularly example 2
--
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu

 

[SPISO]

What if there is not any set number of future days in my data table. For
example, some times we have 4 days in the future and other times we have
scheduled 10 days in the future.

I still only want the chart to show Today in the middle, 4 days in the
future, and 3 days in the past in the chart.

 

[Tushar Mehta]

Suppose you have the data in cols. A and B with row 1 as the header. Further
suppose you have the 'center date' and the number of past days and number of
future days to show in individual cells named as below.

All the names are sheet-level names.

CenterDate =Sheet1!$D$2
NbrFutureDays =Sheet1!$D$4
NbrPastDays =Sheet1!$D$3

Now, create the 3 named formulas below and plot XVals and YVals as the
x-values and y-values of the series.

AllXVals =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
XVals =OFFSET(Sheet1!$A$2,MATCH(Sheet1!CenterDate,Sheet1!AllXVals,0)-1-Sheet1!NbrPastDays,0,Sheet1!NbrPastDays+Sheet1!NbrFutureDays+1,1)
YVals =OFFSET(Sheet1!XVals,0,1)

--
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu