Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving Rows
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving Rows
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 On Aug 11, 1:51*pm, 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. 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving Rows
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" wrote: 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 On Aug 11, 1:51 pm, 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. 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving Rows
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 "teo410" wrote in message ... 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" wrote: 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 On Aug 11, 1:51 pm, 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. 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving data from multiple rows to single rows | Excel Worksheet Functions | |||
Moving Four Rows at a Time | Excel Discussion (Misc queries) | |||
Moving rows | Excel Discussion (Misc queries) | |||
moving alternate rows | Excel Discussion (Misc queries) | |||
moving rows | Excel Discussion (Misc queries) |