Dynamic Chart Timeline

**Scrap74** · 06-29-2010, 12:02 AM

Hi All,

I've had a look around the forum and have seen some solutions that closely resemble what I am chasing but not quite.

I have a situation where I would like to enter an operating cycle in days (D11) that ultimately controls the horizontal scale of the chart.

I have hard coded the data array (J7:O66) to have a maximum 60 days during an operating cycle. I have also used #N/A to force the chart not to plot null values as zero.

So in the example attached, I want to limit the chart to 28 days on the horizontal axis. If I change the Operating Days (D11) to say 35 the chart hz axis should automatically grow/shrink to 35.

Any ideas?

**teylyn** · 06-29-2010, 12:40 AM

Define these range names

CLabels =OFFSET(Sheet1!$L$7,0,0,Sheet1!$D$11,1)
CData1 =OFFSET(CLabels,0,1)
CData2 =OFFSET(CLabels,0,2)
CData3 =OFFSET(CLabels,0,3)

Change your series formula for Data 1 to

=SERIES(Sheet1!$M$5,Example1.xlsx!CLabels,Example1.xlsx!CData1,1)

and for Data 3 to

=SERIES(Sheet1!$O$5,Example1.xlsx!CLabels,Example1.xlsx!CData3,2)

You can do this in the formula bar when the series is selected, or you select the chart, click Design in the Chart Tools, then Select Data

Edit the Horizontal axis labels and enter
=Sheet1!CLabels

Edit the data series and for the Series values put in

=Sheet1!CData1

and

=Sheet1!CData3

cheers