Dynamic Counted Range, Indirect worksheet referencing and WeekNum in one. Getting dizzy!

Hi All,

My issue is that I have a workbook, and I cannot alter it (so cannot add named ranges), but I am allowed to run an analysis from a single sheet within the workbook. I am having trouble due to my dynamic ranges. If you have time, perhaps you could take a look at it?

I need to count records based on a number of criteria. For simplicity I am looking at only one criteria now. The basic formula is>

'=SUMPRODUCT(--(WEEKNUM(--F8:F301)=WEEKNUM($D$1)))

This works fine, but I need to make the range dynamic becuse records might be added or removed. I initilaly thought of the following, but it returns a #VALUE error>

'=SUMPRODUCT(--(WEEKNUM(--INDIRECT($A$1&"!$F$7:$F$"&COUNTA(INDIRECT($A$1&"!B7:B10000")))=WEEKNUM($D$1))))

This part, (INDIRECT($A$1&"!$F$7:$F$"&COUNTA(INDIRECT($A$1&"!B7:B10000")), is supposed to set my range, and it seems to return a correct looking range, but the whole formula does not work when together.

SSH attached.

Thanks for your help!
chomo