Dynamic Charts

[AnilPullagura]

Hello Pros,

Excel 2007

I was trying to create some dynamic charts and was successful on some of them. I was stuck with the below issue.

I need a chart which will track productivity on a daily basis. I now need a chart which shows last 6days of data everyday. So it should include today and past 5 days as the datarange.

I tried using named ranges and offset function, but was unsuccessful in making the datarange to be the last 6 cells in a column


Posted here and hoping for a sure help.

Thanks,
Anil

 

[JoeMo]

Assuming your dates are in column A on Sheet1, you can define a name 'myDates' like this:

=OFFSET(INDEX(Sheet1!$A:$A,MATCH(TODAY(),Sheet1!$A:A,$0)),0,0,-6,1)

and substitute it in the SERIES formula for your chart's x axis like this:

=SERIES(Sheet1!$B$1,TestBook.xlsm!myDates,Sheet1!$B$2:$B$12,1)