In article ,
TRE wrote:

BUT, the following does not work...

B7: =SUM(OFFSET(ADDRESS(2,ROW(A5)-3,1,1,TEXT(B$4,"mmyy")&"PL"),0,0,53,1))

That's because ADDRESS returns a text value, not a reference. INDIRECT
is needed here to return a reference specified by the text string
returned by ADDRESS.

What also does not work, and is the eventual objective, is:

B5:
={SUM((NOT(ISERROR(VALUE(LEFT('0505PL'!$A$2:$A$54, 4)))))*(OFFSET(INDIRECT(ADDR
ESS(2,ROW(A5)-3,1,1,TEXT(B$4,"mmyy")&"PL")),0,0,53,1)))}

Any reason why you can't use something like this...

=SUMPRODUCT(--(ISNUMBER(LEFT(INDIRECT("'"&TEXT(B$4,"mmyy")&"PL'! A2:A54"),
4)+0)),INDIRECT("'"&TEXT(B$4,"mmyy")&"PL'!B2:B54") )

....confirmed with just ENTER

OR

=SUM(IF(ISNUMBER(LEFT(INDIRECT("'"&TEXT(B$4,"mmyy" )&"PL'!A2:A54"),4)+0),I
NDIRECT("'"&TEXT(B$4,"mmyy")&"PL'!B2:B54")))

....confirmed with CONTROL+SHIFT+ENTER