Wasn't aware =CELL("filename",!$A$1) stays fixed after recalculation
(unlike when the reference is omitted). It looks as if similar syntax
can be used to create fixed length arrays such as:

=ROW(!$1:$100)
=ROW(!$A$1:INDEX(!$A:$A,100))
=ROW(TEXTREF("r1:r"&100))

which do not resize or recalculate with sheet editing. This differs
from the sheet reference case where deleting rows reduces array size
(or causes an error when all are deleted) and any change within the
sheet range triggers a recalc. Not surprisingly perhaps, text files
containing such formulas automatically load into an Excel 4 macro
sheet.

On 12 Jul, 07:10, Harlan Grove wrote:
I've just checked this under Excel 2000. Using the following defined
names

local to the worksheet named ' '

' '!_WBNAME:
=TRIM(GET.DOCUMENT(1))

' '!_WSLST:
=SUBSTITUTE(GET.WORKBOOK(1),' '!_WBNAME,"")

global

WSLST:
=' '!$1:$1

_WBWS:
=CELL("Filename",!$1:$65536)

WSNAME:
=MID(_WBWS,FIND("]",_WBWS)+1,32)

Entering the formula

=INDEX(WSLST,MATCH(WSNAME,WSLST,0)+1)

in Sheet1!A1, copying that cell and pasting it into Sheet2!A1 produces
the correct result AND DOESN'T CRASH EXCEL or even display a warning
message. So it looks like XLM functions can be used in one level of
defined names, then another level of defined names referring to ranges
on a utility worksheet could contain formulas referring to the first
level of defined names, and the second level can be used safely.

This indicates that XLM functions can be used safely across all Excel
versions from Excel 5 forward as long as they're never referred to
directly by any worksheet formula.