automatically show data for 3 weeks before & after current week in graph

Hi,

I have the week number of the year, and i am on WEEK6 now. Even though i have the focus vs. actual completion for the whole year (52 weeks), i would like to show only 7 weeks for focus vs. actual completion on my line graphs..

i.e: i am now on WEEK6 so the data on my graph should be only W3, W4, W5, W6, W7,W8,W9 (Week = W).

and let say i am now on W10, so my graph should display W7, W8, W9, W10, W11, W12, W13

I can do this manually by editing my graphs every Monday start of the week. However, i would like this to be automated because i will have more than 10 graphs for show the progress of my work.

I have attached a graph with its data which is very much similar to my real worksheet.

Please kindly advice. Your comments will be very much appreciated.

Hello,

you can do that with dynamic ranges.

Define a range name called currWeek and assign it the formula =weeknum(today())

Then create three named ranges called
chtXLabels =INDEX(Sheet1!$2:$2,MATCH("W"&currWeek,Sheet1!$2:$2,0)-3):INDEX(Sheet1!$2:$2,MATCH("W"&currWeek,Sheet1!$2:$2,0)+3)
chtAC =OFFSET(ChtXLabels,2,0)
chtFC =OFFSET(ChtXLabels,1,0)

Edit your chart series to refer to

FC =sheet1!chtFC
AC =sheet1!chtAC

and set the chart X axis labels to refer to

=sheet1!ChtXLabels

Now, when the week number changes, the chart will update.

You can test this by letting the range name currWeek refer to a cell where you can enter the week number manually.

see attached.

cheers,

change the formula for the range name chartXlabels to

=INDEX(Week_Number!$2:$2,MATCH("W"&currWeek,Week_Number!$2:$2,0)-3):INDEX(Week_Number!$2:$2,MATCH("W"&currWeek,Week_Number!$2:$2,0)+3)


Going forward, it may be a good idea not to use range names that start with "chart". Later versions of Excel consider this as a reserved name. Use "cht" instead, as in my examples above.

See attached with the named ranges working and applied to the two empty charts you attached.

cheers,