Sleeper: Last Row in closed workbook

[mdmackillop]

Is there any way to determine the last row in column A containing data in a closed workbook. It is assumed that there may be gaps in the data.

[MWE]

Is there any way to determine the last row in column A containing data in a closed workbook. It is assumed that there may be gaps in the data.
and opening it and checking for the last populated cell in Col A is not allowed?

[Jacob Hilderbrand]

Are we talking about numbers?


=MATCH(9.99999999999999E+307, 'C:\MyPath\[Book2.xls]Sheet1'!$A:$A,TRUE)

[XL-Dennis]

No need to open it as Jake points out ;)

And for text values You can test to use:

=MATCH(REPT("z",255), 'C:\MyPath\[Book2.xls]Sheet1'!$A:$A,TRUE)

Kind regards,
Dennis

[Bob Phillips]

No need to open it as Jake points out ;)

And for text values You can test to use:


=MATCH(REP("z",255), 'C:\MyPath\[Book2.xls]Sheet1'!$A:$A,TRUE)



Typo, should be


=MATCH(REPT("z",255), 'C:\MyPath\[Book2.xls]Sheet1'!$A:$A,TRUE)

For mixed values, try


=LOOKUP(2,1/(1-ISBLANK( 'C:\MyPath\[Book2.xls]Sheet1'!A1:A65535)),( 'C:\MyPath\[Book2.xls]Sheet1'!A1:A65535))

[XL-Dennis]

Typo, should be...


Thanks and FYI, REP is the Swedish name for the REPT-function. You guys with English as the first language have it much more easier then the rest of us ;)

Kind regards,
Dennis