OFFSET using ADDRESS for the reference argument

[Guest]

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.

 

[Domenic]

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