Sum of 6 moving cells

[ledzepe]

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.

 

[Bob Phillips]

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]

Oh... each unit have their own sheet, example unit 1 is identified as
sheet "Unit1", and so on...

 

[Guest]

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.

 

[Bob Phillips]

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]

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...