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