Indirect Use of XLM in Excel 2000 and Prior
Harlan Grove
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.
Ron Coderre
What happened? Did figuring that out go from an idle curiosity to an
outright quest for you?
***********
Best Regards,
Ron
XL2002, WinXP
Harlan Grove
...
>What happened? Did figuring that out go from an idle curiosity to an
>outright quest for you?
So you read my post from a few days ago? If I'd had an older version of
Excel running on a nearby machine at the time, I'd have tested it then. Last
night was my first chance to use such a machine to test this.
A bit more than idle curiosity. I use relative worksheet addressing, and
I've had to use udfs. Now it appears I can scrap those udfs, but after I
check relative recalc times.
Ron Coderre
your reputation for precision and efficiency. You had a concept you believed
should work and you pursued it to a successful end. THAT I applaud. Like I
said: Nice work. (I hope the performance of that method is favorable)
***********
Best Regards,
Ron
XL2002, WinXP
"Harlan Grove" wrote:
> "Ron Coderre" <RonCo...@discussions.microsoft.com> wrote...
> ....
> >What happened? Did figuring that out go from an idle curiosity to an
> >outright quest for you?
> ....
Lori
(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.
Harlan Grove
...
>>local to the worksheet named ' '
>
>solely, name equal to CHAR(32). Since I don't understand why one
>would do that, . . .
It's intended to be a hidden worksheet containing worksheet-level
named formulas and global named ranges. Formulas in other worksheets
would refer to the named ranges but not ranges in the worksheet. Using
' ' (without the single quotes) uses up a potential worksheet name
that is unlikely to be used otherwise, which means it doesn't tie up a
worksheet name I might want to use.
There's nothing necessary about this. If you want to, you could name
the worksheet _ or Utility or ThisShouldBeVeryHidden.
> . . . When I try other constructions such as '' (single
>quote-single quote), Excel says they are invalid names.
...
Single quotes aren't part of the worksheet's name. They delimit
worksheet names that include spaces. Two single quotes in sequence
would be equivalent to a worksheet name that has no characters at all,
the same as the result of the formula =LEFT("Whatever",0). That's not
a valid worksheet name. Worksheet names need to have *ONE* or more
valid characters.
>>global
>>WSLST:
>>=' '!$1:$1
>
>Isn't this local? And I take it refer to Row 1 of sheet space-bar.
No! This is GLOBAL. Its exists so that formulas in OTHER worksheets
may refer to this named range. Wouldn't that intended use be simpler
if the name were global scope?
>_WBWS and WSNAME I understand, though not why the $1:$65536 range.
$1:$65536 remains unaffected no matter which or how many rows, columns
or cells you insert or delete. Since CELL only uses the top-left cell
of its optional second (range reference) argument, this doesn't do any
more work than CELL("Filename",!$A$1).
>>=INDEX(WSLST,MATCH(WSNAME,WSLST,0)+1)
>
>Returns #N/A for me. It looks to be indexing a list of sheet
>names, but I don't see how it was populated. See above.
...
I missed a step in my original posting. Once WSLST is defined, select
it (it's a range, ' '!1:1) and enter the array formula
=_WSLST
Harlan Grove
...
>isn't true when you rename the worksheet. Doing so breaks
>_WBNAME.
>GET.DOCUMENT(1) actually returns WorkBookName]WorkSheetName
...
You're right. I should have defined _WBNAME as
="["&GET.DOCUMENT(88)&"]"
Harlan Grove
...
>Still got Kyd hanging around? Me too.
...
Nope. Just using the XLM help files (finally - should have perhaps referred
to them earlier this week, but I didn't have them on the PC where I tested
this under Excel 2000).