looking at for the latest date

[Guest]

My problem is this.

Sheet 1 is a ledger which contains plot numbers of houses on a new building
site. I look at this ledger to monitor the sites progress.

I have tablulated the data.
From Cell B1 - P1 are the plot numbers and Cell A2 - A21 are Item
Descriptions.
In each of the cells in the table the formula mentioned is entered.

=IF(SUMPRODUCT(--('WE 30.07.04'!$A$6:$A$82=4),

As you can see, the formula will only pick up the required data from the
worksheet 'WE 30.07.04'.

I would like to be able to create a new worksheet for each week ending date
and therefore would like to know if it is possible for the formula in the
Cells of my table to search for the required data but in the worksheet with
the most recent date?

Is this possible?

 

[Harlan Grove]

Sheet 1 is a ledger which contains plot numbers of houses on a new building
site. I look at this ledger to monitor the sites progress. ....
In each of the cells in the table the formula mentioned is entered.

=IF(SUMPRODUCT(--('WE 30.07.04'!$A$6:$A$82=4),
--('WE 30.07.04'!$C$6:$C$82=$A13)),"X","")

As you can see, the formula will only pick up the required data from the
worksheet 'WE 30.07.04'.

I would like to be able to create a new worksheet for each week ending date
and therefore would like to know if it is possible for the formula in the
Cells of my table to search for the required data but in the worksheet with
the most recent date?

If you want the worksheet reference to be dynamic, then you need to use
INDIRECT. If the desired date were given by the defined name WkEnding, the
definition of which I'll ignore for the moment, you could use

=IF(SUMPRODUCT(--(INDIRECT(TEXT(WkEnding,"\'\W\E dd.mm.yy""'!A6:A82"""))=4),
--(INDIRECT(TEXT(WkEnding,"\'\W\E dd.mm.yy""'!C6:C82"""))=$A13)),"X","")

Name any blank cell WkEnding and enter 30-July-2004 and this formula should
give the desired result for the worksheet 'WE 30.07.04'. Change the entry to
23-July-2004 and this formula should change to the result for the worksheet
'WE 23.07.04'.

You could leave WkEnding a manual entry, so you could change it to review
historical results ad hoc, or you could give it a formula. If the previous
week ending worksheet should be ready every Saturday, you could use the
formula

WkEnding:
=TODAY()-WEEKDAY(TODAY()+1)

which will always give the most recent Friday, evaluating to 7 days prior
when TODAY() is a Friday. If your week ending worksheet would be available
on Friday after, say, 1:00 PM, try

WkEnding:
=NOW()-13/24-WEEKDAY(NOW()+3-13/24,3)