Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 53
Default source data needs to be updated weekly

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

  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,722
Default source data needs to be updated weekly

See Jon Peltier's article on Dynamic Charting. His example is for 12 months,
but you can easily modify it for any time frame.

http://peltiertech.com/Excel/Charts/DynamicLast12.html

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Judy" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 53
Default source data needs to be updated weekly

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?

"Luke M" wrote:

See Jon Peltier's article on Dynamic Charting. His example is for 12 months,
but you can easily modify it for any time frame.

http://peltiertech.com/Excel/Charts/DynamicLast12.html

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Judy" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,722
Default source data needs to be updated weekly

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Judy" wrote:

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?

"Luke M" wrote:

See Jon Peltier's article on Dynamic Charting. His example is for 12 months,
but you can easily modify it for any time frame.

http://peltiertech.com/Excel/Charts/DynamicLast12.html

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Judy" wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 3,346
Default source data needs to be updated weekly

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.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Judy" wrote:

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?

"Luke M" wrote:

See Jon Peltier's article on Dynamic Charting. His example is for 12 months,
but you can easily modify it for any time frame.

http://peltiertech.com/Excel/Charts/DynamicLast12.html

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Judy" wrote:

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



  #6   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 3
Default source data needs to be updated weekly

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



"Judy" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Chart displaying weekly data group in months without weekly labels smcgee01 Charts and Charting in Excel 7 September 11th 08 10:53 PM
Can I preserve chart formatting when the source data is updated? JasenD Charts and Charting in Excel 3 June 19th 06 01:38 PM
Pivot Table data source "data source contains no visible tables" Jane Excel Worksheet Functions 0 September 29th 05 08:28 PM
What if I DON'T want the chart to be updated when source data changes? arhooley Charts and Charting in Excel 2 June 9th 05 05:07 AM
Applying a currency rate that doesn't change when source updated Redsmartie Excel Worksheet Functions 3 April 1st 05 06:54 PM


All times are GMT +1. The time now is 02:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"