Moving Rows

[teo410]

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.

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
Tue 18.08.2009 5 0 0 0
0 5

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?

 

[Pete_UK]

Where do the numbers come from? Do you manually type them in each day?

You could have one sheet which is used for all the data, and then have
another sheet which just displays the previous seven days' data
depending on TODAY(). Is that what you want?

Hope this helps.

Pete

 

[teo410]

Yes they are manually input at the beginning of each day ONLY IF something
has changed but the last day has to be put in everyday regardless.

This sheet is linked into a PPT that is presented everymorning so it would
be easy to keep it in one sheet in one table

 

[Pete_UK]

Well then, your Input sheet will have the same format as laid out in your
example, with Day in column A, Date in column B etc., and I assume that you
have a header row so that the data begins on row 2. You will add each day's
data to the next available row. Ensure that column B is formatted as a date,
as Excel will take this type of input "11.08.2009" as being a text value as
the normal separator for dates is "/". You can pre-format column B as a
date, and indeed you could put a formula in there to add 1 to the previous
date and so you will not have to type that in each day - something like this
in B3:

=IF(B2+1<=TODAY(),B2+1,"")

Then copy this down. You will need to put a starting date in B2.

In your Display sheet you will need a header row, so in B2 put this formula:

=TODAY()-7+ROW(A1)

and copy this to B8. In A2 you can have this formula:

=TEXT(B2,"ddd")

and copy this down to A8.

Then in C2 you can have this formula:

=VLOOKUP($B2,Input!$B:$H,COLUMN(B1),0)

which you can copy across to H2 and then copy C2:H2 down to row 8.

The display then automatically adjusts with each new day.

Hope this helps.

Pete