Auto Update Chart Range Every Monday

[Justair07]

Hello Everyone,

I wasn't sure which topic to post this on. I don't know the optimal solution to accomplish this. I have a chart that I want the data to auto update every Monday. For example I have data that I want to display for this week only (8/25/2014 - 8/31/2014) Monday through Sunday. Next Monday I want the chart to only show data only for 9/1/2014 - 9/7/2014, and so on.

I have know idea how to automate this. If you look at my sample you will see what I'm talking about. I don't know if this can be done with a formula or if a macro is needed.

Here is my sample.

Thank you in advance to anyone willing to help me out with this.

- Justin

[PeteABC123]

Sample1.xlsxHere's what I came up with:
On the SQDCI DR 24 tab, insert a helper column in col A. Beginning on that tab, in A5, enter this: =WEEKNUM(B5,2)
and copy down. This identifies the week number of your workweek which begins on a Monday and lasts 7 days.
In the Sheet1 tab, in C1, enter the number of the week you want reported. Enter 35.
In C3, enter =SUMIF('SQDCI DR 24'!$A:$A,$C$1,'SQDCI DR 24'!$C:$C)
This updates your data series summary range to the week number identified in C1. . .in this case week 35.

Follow this logic, and copy the formula entered into C3 into the other shift and department summary cells, adjusting the range summed from the source data tab.

This will update your chart to the desred week by changing the week number entered in C1.

Hope that helps.
Pete

[Justair07]

Thank you Pete. Perfect solution!

[PeteABC123]

You can mark the thread as SOLVED if this works. . .add to reputation if you like.
Pete