Apologies, correction to formula:

Put in B2:

=SUMPRODUCT((INDIRECT("'"&B$1&"'!$AN$10:$AN$40")= TODAY()-$A2)*(INDIRECT("'"&B$1&"'!$AN$10:$AN$40")<TODAY()) ,'1'!$AO$10:$AO$40)

should instead be:

Put in B2:

=SUMPRODUCT((INDIRECT("'"&B$1&"'!$AN$10:$AN$40")= TODAY()-$A2)*(INDIRECT("'"&B$1&"'!$AN$10:$AN$40")<TODAY()) ,INDIRECT("'"&B$1&"'!$AO$10:$AO$40"))

(corrected the last part of the formula, forgot the INDIRECT(...) ..)

Note: In the preceding suggestion, the range AP10:AP40 was ignored. If you
also have "hours worked" in that range to be conditionally summed in the same
manner, just make a duplicate of the sheet: Summary, and change the range to
sum to point to "AP10:AP40" instead,

i.e. Put in B2 (in the duplicated sheet):

=SUMPRODUCT((INDIRECT("'"&B$1&"'!$AN$10:$AN$40")= TODAY()-$A2)*(INDIRECT("'"&B$1&"'!$AN$10:$AN$40")<TODAY()) ,INDIRECT("'"&B$1&"'!$AP$10:$AP$40"))

Then fill across and down to populate the table as before

This will return the corresponding results for the range "AP10:AP40"

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----