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

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

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

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

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

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