Changing chart data weekly

**Befuddled** · 07-27-2010, 03:42 AM

I have a chart (well 2 on the same page) that the data needs to be adjusted, to show only the previous 4 weeks, when I am ready to print it. Is there any way to do this automatically? I have attached a copy of this file.

**Befuddled** · 07-27-2010, 11:04 PM

here is the file

**teylyn** · 07-28-2010, 05:39 AM

Your data has links to external sources, your data structure is hard to understand, the chart is twice as big as my monitor and the data cells are tiny and hard to read with the dark gray background.

I don't know what you want to achieve by "the previous 4 weeks" and I don't like receiving visitor messages or PMs asking for attention to threads. If I find the time and if the question is presented in a way that makes the problem accessible, I will attempt to answer it.

If I have to wade through a workbook and figure out complicated setups and overcome horrible color schemes, I won't.

Present your problem in a way that is easier to understand. Include a data sample that represents your data and outlines what you want to achieve (mock it up manyually), or, if you feel you've already done that, outline what part of your data you would like to chart. I'm not a mind-reader.

cheers

**Befuddled** · 07-29-2010, 08:30 PM

Teylyn, I was not meaning to overstep, when I msg to you about looking at my thread. I had accidentally posted the thread in the wrong spot, and it appeared that no one had noticed it there. Thank you for taking the time to check it out anyway.

I have cut most of the data out of the file, leaving just the pertinent information.

I would like to know if there is a way to automatically adjust the range a chart looks at to get its data. In this case, i have to go to:

Chart Tools - Design - Select Data
then click Edit... with "Total D/M" highlighted.
and adjust the "Series Values"

I am selecting the previous 4 weeks data. in this case K32:K35 is the target (corresponding to the data for weeks [6/28/10-7/2/10]:[7/19/10-7/23/10]),

I would like it to adjust to the data for weeks [7/5/10-7/30/10], Target K33:K36 ,when I run this on 8/2/10.

I hope this is easier to decipher

**teylyn** · 07-31-2010, 04:58 AM

Get rid of the empty columns in your data table, then construct a pivot table, where you can filter on the data to report.

For a starting point with Pivot tables and Pivot charts, refer to http://peltiertech.com/Excel/Pivots/pivotstart.htm