Indirect Use of XLM in Excel 2000 and Prior

[Harlan Grove]

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.

 

[Ron Coderre]

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]

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]

Obviously, that was just a rhetorical question, Harlan. I'm very aware of
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

 

[Lori]

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.

 

[Captain_Nemo]

Hello Harlan -

I want to thank you for this post. I really want to understand this
capability, and I can't make it work. It will be a huge shortcut.

local to the worksheet named ' '

I take this to mean there is a worksheet named by the space-bar solely,
name equal to CHAR(32). Since I don't understand why one would do that,
I also take this to be the root of my whole problem. When I try other
constructions such as '' (single quote-single quote), Excel says they
are invalid names.

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

Returns the trimmed document name. In my case [Grove1.xls]

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

Returns a 1xN horizontal array of N worksheets' sheet names.
[Grove1.xls] substituted out.

global

WSLST:
=' '!$1:$1

Isn't this local? And I take it refer to Row 1 of sheet space-bar.

_WBWS and WSNAME I understand, though not why the $1:$65536 range.

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

Sorry to be so dense.

....best, Capt N.

--
Email to (e-mail address removed) (yes, you can so figure it out) ;-]

Scream and shout and jump for joy! I was here before Kilroy!

Sorry to spoil your little joke. I was here but my computer broke. ---Kilroy

 

[Harlan Grove]

(e-mail address removed) wrote...
....

I take this to mean there is a worksheet named by the space-bar
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.

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

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

 

[Captain_Nemo]

Hello Harlan -

Thank you. I got it to work as you did it. However

There's nothing necessary about this. If you want to, you could name

the worksheet _ or Utility or ThisShouldBeVeryHidden.

isn't true when you rename the worksheet. Doing so breaks _WBNAME.
GET.DOCUMENT(1) actually returns [WorkBookName]WorkSheetName

The TRIM() in _WBNAME nicely removes the trailing space from a worksheet
named by the space-bar, but doesn't handle for instance
[Grove1.xls]Utility. I changed _WBNAME to
=LEFT(GET.DOCUMENT(1),FIND("]",GET.DOCUMENT(1))) and got the
functionality back.

And I now better understand local and global names...if the worksheet
name is in the name, it's local. If it's in the reference, it's global.

Got it. Thanks again.

....best, Capt N.

--
Email to (e-mail address removed) (yes, you can so figure it out) ;-]

Scream and shout and jump for joy! I was here before Kilroy!

Sorry to spoil your little joke. I was here but my computer broke. ---Kilroy

 

[Harlan Grove]

(e-mail address removed) wrote...
....

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)&"]"

 

[Captain_Nemo]

(e-mail address removed) wrote...
...

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)&"]"

Still got Kyd hanging around? Me too.

Thanks.

--
Email to (e-mail address removed) (yes, you can so figure it out) ;-]

Scream and shout and jump for joy! I was here before Kilroy!

Sorry to spoil your little joke. I was here but my computer broke. ---Kilroy

 

[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).

 

[Captain_Nemo]

...

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

Which is a good thing...Kyd&Kinata don't have GET.DOCUMENT(88) in their
table.

Thanks for the help.

....best, Capt N.

--
Email to (e-mail address removed) (yes, you can so figure it out) ;-]

Scream and shout and jump for joy! I was here before Kilroy!

Sorry to spoil your little joke. I was here but my computer broke. ---Kilroy