I have a workbook that has data on 104 worksheets (2 years).

The worksheets are labeled 1 through 104, easy enough

Every week data is populated to one of the worksheets. This week it
was worksheet 35, next week it will be worksheet 36 and so on.

On the report (Summary) worksheet I have a cell ( CO2 ) that has to
search for a value on the worksheets and put the newest data in.

The formula would be something like =(SUMPRODUCT(--('35'!$C$2:$C$400=$A
$1),--('35'!$A$2:$A$400=$B2),'35'!$H$2:$H$400))/100

The tricky part becomes when the week changes over.

In other cells I have used =IF('38'!$A$1="","",((SUMPRODUCT(--('38'!$C
$2:$C$400=$A$1),--('38'!$A$2:$A$400=$B2),'38'!$H$2:$H$400))/100))

But in this instance I need CO2 to populate the data with the matching
cell from the latest week. It somehow needs to determine there is no
data in worksheets 36-104 and seacrh worksheet 35 for the matching
data. Next week, after the data load, it needs to determine there is
now data in worksheet 36 and search worksheet 36 for the matching data
instead of worksheet 35.

Thanks in advance for any assitance you can provide.

Don Flak

An idea...

If the data is updated starting from a specific base date and the update
occurs on a specific weekday then you could probably count the number of
update weekdays since the base date to determine which sheet to reference.

For example, if the data is updated every Monday and 35 Monday's have passed
since the starting base date then you should be able to use this as the
basis of which sheet to reference.

???

--
Biff
Microsoft Excel MVP


wrote in message
...
I have a workbook that has data on 104 worksheets (2 years).

The worksheets are labeled 1 through 104, easy enough

Every week data is populated to one of the worksheets. This week it
was worksheet 35, next week it will be worksheet 36 and so on.

On the report (Summary) worksheet I have a cell ( CO2 ) that has to
search for a value on the worksheets and put the newest data in.

The formula would be something like =(SUMPRODUCT(--('35'!$C$2:$C$400=$A
$1),--('35'!$A$2:$A$400=$B2),'35'!$H$2:$H$400))/100

The tricky part becomes when the week changes over.

In other cells I have used =IF('38'!$A$1="","",((SUMPRODUCT(--('38'!$C
$2:$C$400=$A$1),--('38'!$A$2:$A$400=$B2),'38'!$H$2:$H$400))/100))

But in this instance I need CO2 to populate the data with the matching
cell from the latest week. It somehow needs to determine there is no
data in worksheets 36-104 and seacrh worksheet 35 for the matching
data. Next week, after the data load, it needs to determine there is
now data in worksheet 36 and search worksheet 36 for the matching data
instead of worksheet 35.

Thanks in advance for any assitance you can provide.

Don Flak