#1   Report Post  
Old August 11th 09, 08:56 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 14
Default Rolling Week

Day Date A B C D E T Tue 11.08.2009 6 6 0 0 0 12
Wed 12.08.2009 4 2 0 1 1 8
Thu 13.08.2009 6 0 0 2 0 8
Fri 14.08.2009 4 6 5 0 0 15
Sat 15.08.2009 0 0 0 0 0 0
Sun 16.08.2009 0 0 0 0 0 0
Mon 17.08.2009 5 0 0 0 0 5

I have a table similar to the one above. It is a 7 day working plan and is a
rolling plan also e.g. after tues will go to the bottom for next week etc etc.

I've managed to get the days and dates to "roll" by using the =TODAY()
function annd +1 +2 etc but I was wondering if anyone could help me with the
rest of the data? It would be great if the data would follow the day it
linked to and the last days data all appear as zero. Anyone have any ideas?

  #2   Report Post  
Old August 11th 09, 11:42 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 915
Default Rolling Week

teo410 wrote:
Day Date A B C D E T Tue 11.08.2009 6 6 0 0 0 12
Wed 12.08.2009 4 2 0 1 1 8
Thu 13.08.2009 6 0 0 2 0 8
Fri 14.08.2009 4 6 5 0 0 15
Sat 15.08.2009 0 0 0 0 0 0
Sun 16.08.2009 0 0 0 0 0 0
Mon 17.08.2009 5 0 0 0 0 5

I have a table similar to the one above. It is a 7 day working plan and is a
rolling plan also e.g. after tues will go to the bottom for next week etc etc.

I've managed to get the days and dates to "roll" by using the =TODAY()
function annd +1 +2 etc but I was wondering if anyone could help me with the
rest of the data? It would be great if the data would follow the day it
linked to and the last days data all appear as zero. Anyone have any ideas?


Use a separate worksheet for the data entry, set up just as above but
with /static/ dates--no TODAY() formula. Add a new row for each date.
This worksheet will grow over time. Delete old rows if you wish, but
this isn't necessary. Let's call this your data table.

Then use your idea above to set up the rolling 7-day window to your data
table. The values A, B, C, etc. can be obtained from the data table by
using VLOOKUP.

Now when TODAY changes, the window will automatically fetch the
appropriate values from the data table.


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
how do i calculate a 4 week rolling average Charliechoo Excel Discussion (Misc queries) 1 October 16th 07 09:14 PM
How can I setup a rolling 13 week spreadsheet? cshel Excel Worksheet Functions 1 April 15th 07 03:16 AM
help with 12 week rolling average [email protected] Excel Worksheet Functions 3 January 29th 07 12:34 PM
Rolling 12 week calculations Throwme A Frigginbone Excel Worksheet Functions 2 January 9th 07 09:37 AM
Calculation using rolling 4 week amounts Janet T Excel Discussion (Misc queries) 3 November 15th 05 09:16 PM


All times are GMT +1. The time now is 03:20 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017