Why does the following work...
B4: May-05 ....this is a date number 38473
B6: =ADDRESS(2,ROW(A5)-3,1,1,TEXT(B$4,"mmyy")&"PL")
B7:
=SUM(OFFSET(INDIRECT(ADDRESS(2,ROW(A5)-3,1,1,TEXT(B$4,"mmyy")&"PL")),0,0,53,1))
which looks up a range on a sheet called 0505PL and sums it. Note that I do
not want reference in the OFFSET to be the CONTENTS of the outcome of the
ADDRESS formula - it IS the outcome of the ADDRESS formula, an address. Note
that B6 is not used in the above, bit it DOES produce the correct argument
for reference in the OFFSET function of B7. Using just INDIRECT(B6) for the
reference arguemnt in OFFSET also works fine, as expected.

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))

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(ADDRESS(2,ROW(A5)-3,1,1,TEXT(B$4,"mmyy")&"PL")),0,0,53,1)))}

....entered as an array function to add a condition. The left side of the
product does not yet incorporate the flexibility attempted in the right hand
side. When the right hand side is "simple" as in the left, it also works
fine. i.e.

B5:
={SUM((NOT(ISERROR(VALUE(LEFT('0505PL'!$A$2:$A$54, 4)))))*('0505PL'!$B$2:$B$54))}

works fine.