source data needs to be updated weekly

  • Thread starter Thread starter Judy
  • Start date Start date
J

Judy

I have a spreadsheet tracking documents through their approval cycle. I

need to produce a chart weekly, always showing the most recent 3 weeks of
data. So, the chart needs to drop off the oldest week, and add the newest
week's values, showing the totals in each stage of review, comparing the
current week and the past 2 weeks. Here's a sample of my spreadsheet:

A B C D
E F
1 Week of Author Review 1 Review 2 Review 3 Approved
2 5/28/2009 24 130 25 8 194
3 6/4/2009 20 134 26 1 200
4 6/11/2009 15 135 11 4 201
5 6/18/2009 20 150 9 6 202
6 6/25/2009 15 153 8 8 206

I tried recording a macro to Remove the earliest date's values (5/28 in
example above) and to Add the lastest date's values (6/18 above) to the
chart; the macro works for the first time I have to update, but that's the
only week it works. For the following week (6/25 above), the macro does
remove the 6/4 week of values fine, but it still just adds the new week as
the 6/18 week.

Is there an easier way to update my chart weekly or do I have to do it
manually each week?

Thanks for any help,

Judy
 
Thanks Luke. I went to that site and did the formulas as instructed; however,
it's not displaying how I need it to. In the example, it is displaying the
chart wiht the dates at the bottom and comparing the 2 values. I need to
compare the values by the 3 week period, so I need the 3 weekly dates to be
my legend and the category names to be at the bottom of the chart. Plus, it
also displayed ALL the dates, not just the last 3.

I tried switching it around, but that didn't work either. I'm still lost in
this. Any other ideas?
 
Your setup will require more named ranges, but not impossible. From the
beginning...

The following are Named Range and their equations

Label1
=OFFSET('Sheet1'!$A$1,COUNTA('Sheet1'!$A:$A)-3)

Label2
=OFFSET(Label1,1,0)

Label 3
=OFFSET(Label2,1,0)

Data1
=OFFSET(Label1,0,1,1,5)

Data2
=OFFSET(Data1,1,0)

Data3
=OFFSET(Data2,1,0)


Now for your chart. Create chart, pick type, series tab, add a series. Using
Jon's method of first clicking in a cell, then replacing cell reference,
place the reference to Label1 as Series 1 name, then Data1 as Series 1
y-values.

Repeat method for Series 2 and 3.
For x-category values, select appropriate header cells, e.g.
=Sheet1!$B$1:$F$1


Now, Data1 is the range to manipulate if you need to change what all your
are graphing. The first number (currnetly 1) determines how far to offset
from column A. The last number (currently 5) determines how many columns to
plot. So, if you just want the 3 reviewer's graphed, Data1 becomes:
=OFFSET(Label1,2,0,1,3)

and everything else can remain the same.
 
Hi,

Here is another method:


1. Select the data you are plotting and choose Data, List, Create List, OK.
2. Choose Data, Filter, AutoFilter (you may not steps 2-5, see my comment
below)
3. Open the AutoFilter in the Week of Author column and choose Custom
4. From the first drop down pick Greater than or equal to and pick a date
from the dropdown on the right
5. From the second row open the left hand drop down and pick Less than or
equal to and pick a date from the dropdown to its right.

As you add new data below the last row it will automatically be added to the
chart. You then repeat steps 3-5.

If you all want to do is delete the rows you don't want and and add new ones
- always plotting all the data, then you don't even need the AutoFilter, just
create the list and plot the chart from the list. Delete rows you don't want
and enter new ones below the last row. The chart will automatically plot
your data.
 
Judy

I generate 8 very involved reports every week.
I wrote scripts/macros but found I was always tweaking this or that just to
keep things working. Repopulating data tables was my goal and since the
reports are generated from other excel data files macros required opening
other files just to link to the data.

What worked for me - I built a data table which uses the VLoolup function.
My tables of data query the data range from a single date cell. I type in the
date of interest and hit enter. My data tables have VLookup in the formula.
The cells all update with current data located in several other files on our
network. My report data and charts are on another tab which remain unchanged
other than presenting the updated data. Chart titles and range values are all
linked to the data tables which all use VLookup so they automatically change,
very nice.

Total time to update the chart: less time than it takes to open the file.
Once a week I spent about 15-minutes updating the data reports.
Everyone thinks I spend hours and hours generating these reports.
If this works for you remember - don't tell anyone how much time you saved,
everyone will think you are still overworked.

-Steve
 
Back
Top