Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum of 6 moving cells
Good day to all, Sheet1: Column A is the date (from Jan 1, 2005 to the current date), Col B contains the hours worked on that date and Col C is hours worked for the last 12 days. I manually type in the hours worked everyday on Col B and there is a formula on Col C to add the last 12 days. There's about 45 units that I keep track of the hours worked. How can I show on a summary sheet, the hours worked of each unit as of yesterday? My problem is that the cell that totals the last 12 days moves down one cell everyday. -- ledzepe ------------------------------------------------------------------------ ledzepe's Profile: http://www.excelforum.com/member.php...o&userid=25207 View this thread: http://www.excelforum.com/showthread...hreadid=495556 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum of 6 moving cells
Where are units identified, and does this mean that you might work on
multiple units on the same day, that is multiple rows for a date? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ledzepe" wrote in message ... Good day to all, Sheet1: Column A is the date (from Jan 1, 2005 to the current date), Col B contains the hours worked on that date and Col C is hours worked for the last 12 days. I manually type in the hours worked everyday on Col B and there is a formula on Col C to add the last 12 days. There's about 45 units that I keep track of the hours worked. How can I show on a summary sheet, the hours worked of each unit as of yesterday? My problem is that the cell that totals the last 12 days moves down one cell everyday. -- ledzepe ------------------------------------------------------------------------ ledzepe's Profile: http://www.excelforum.com/member.php...o&userid=25207 View this thread: http://www.excelforum.com/showthread...hreadid=495556 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum of 6 moving cells
Oh... each unit have their own sheet, example unit 1 is identified as sheet "Unit1", and so on... -- ledzepe ------------------------------------------------------------------------ ledzepe's Profile: http://www.excelforum.com/member.php...o&userid=25207 View this thread: http://www.excelforum.com/showthread...hreadid=495556 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum of 6 moving cells
You could do this... in column A, on the same row that has your 12 day total,
type something like "Unit 1 total". On your summary page, type the same title and then use a VLOOKUP, or a SUMIF to bring the total over to the summary. Since you are inserting cells (or rows) don't lock the arrays in the formulas and they will pick up the total line as it moves down the page. "ledzepe" wrote: Oh... each unit have their own sheet, example unit 1 is identified as sheet "Unit1", and so on... -- ledzepe ------------------------------------------------------------------------ ledzepe's Profile: http://www.excelforum.com/member.php...o&userid=25207 View this thread: http://www.excelforum.com/showthread...hreadid=495556 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum of 6 moving cells
I've got it. For every sheet, I need to make a formula: =VLOOKUP((TODAY()-1),$A$2:$C$30, 2) for today minus 1. Next cell: =VLOOKUP((TODAY()-2),$A$2:$C$30, 2) for today minus 2 and so on until today minus 12. I then get the sum of the 12 cells and wah lah, I've got the hour worked on one summary sheet. THANKS... -- ledzepe ------------------------------------------------------------------------ ledzepe's Profile: http://www.excelforum.com/member.php...o&userid=25207 View this thread: http://www.excelforum.com/showthread...hreadid=495556 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum of 6 moving cells
Does this work for you?
=SUMPRODUCT(--('Unit 1'!A2:A500<=TODAY()-1),--('Unit 1'!B2:B500)) -- HTH RP (remove nothere from the email address if mailing direct) "ledzepe" wrote in message ... Oh... each unit have their own sheet, example unit 1 is identified as sheet "Unit1", and so on... -- ledzepe ------------------------------------------------------------------------ ledzepe's Profile: http://www.excelforum.com/member.php...o&userid=25207 View this thread: http://www.excelforum.com/showthread...hreadid=495556 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
moving cells based certain value | New Users to Excel | |||
requires that merged cells must be identically sized? | Excel Discussion (Misc queries) | |||
Help adding text values | Excel Worksheet Functions | |||
instead of moving cells my arrow keys scroll the page | Excel Discussion (Misc queries) |