Hello,
I have the following array formula.
=SUM((MONTH('ADUNITS CSV'!$A$2:$A$29848)=MONTH($A15))*(YEAR('ADUNITS CSV'!$A$2:$A$29848)=YEAR($A15))*('ADUNITS CSV'!$G$2:$G$29848)*(ISNUMBER(SEARCH(B$1,'ADUNITS CSV'!$B$2:$B$29848))))
Instead of inputting 29848 (as in $A$29848) manually, I'd like it to be the result of the following formula.
MATCH("Totals",'ADUNITS CSV'!A:A,0)
Basically I need to tell Excel that the last row number in the range I need is the result of the MATCH formula.
I tried using the ADDRESS formula in combination with CONCATENATE as follows
="'ADUNITS CSV'!$A$2:" & ADDRESS(MATCH("Totals",'ADUNITS CSV'!A:A,0),1) which results in
'ADUNITS CSV'!$A$2:$A$29848 but Excel doesn't seem to interpret this result as a range, but as text so
=MONTH("'ADUNITS CSV'!$A$2:" & ADDRESS(MATCH("Totals",'ADUNITS CSV'!A:A,0),1)) results in an error.
Any ideas?
Thanks
Bookmarks