Ron de Bruin covers how to do that.

http://www.rondebruin.nl/copy7.htm

"Geoff K" wrote:

Hi
Getting data from a closed wbook.
These wsheet formulae work fine on numerical fields but not on text.

Gets from a closed wbook the value in the last used cell of a column.
=LOOKUP(99^99,'C:\Path\[File.xls]Sheet1'!A:A)

Gets from a closed wbook the last used row number of a column.
=MATCH(99^99,'C:\Path\[File.xls]Sheet1'!A:A)

But if either meet with a text field or even if 99^99 is substituted with
"ZZZ" then Excel goes into an infinite loop.

How can the formulae be made universal to look for either numeric or text
fields? Or if that is not possible then how might it made to work in a text
field?

T.I.A

Geoff