In article <[email protected]>,
TRE <[email protected]> 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
Bookmarks