View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre Ron Coderre is offline
external usenet poster
 
Posts: 698
Default Indirect Use of XLM in Excel 2000 and Prior

Nice work, Harlan

What happened? Did figuring that out go from an idle curiosity to an
outright quest for you?

***********
Best Regards,
Ron

XL2002, WinXP


"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.