Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Use of XLM in Excel 2000 and Prior
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Use of XLM in Excel 2000 and Prior
"Ron Coderre" wrote...
.... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Use of XLM in Excel 2000 and Prior
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 "Harlan Grove" wrote: "Ron Coderre" wrote... .... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Use of XLM in Excel 2000 and Prior
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Use of XLM in Excel 2000 and Prior
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 (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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Use of XLM in Excel 2000 and Prior
wrote...
.... 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, . . . 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Use of XLM in Excel 2000 and Prior
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 (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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Use of XLM in Excel 2000 and Prior
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)&"]" |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Use of XLM in Excel 2000 and Prior
In article . com,
Harlan Grove wrote: 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 (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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Use of XLM in Excel 2000 and Prior
wrote...
.... 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). |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Use of XLM in Excel 2000 and Prior
In article ,
"Harlan Grove" wrote: wrote... ... 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). 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 (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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
edate - Prior to Excel 2003 | Excel Worksheet Functions | |||
Dates prior to 1900 in Excel | Excel Discussion (Misc queries) | |||
XML use with Excel prior to Version 2003 | Excel Discussion (Misc queries) | |||
Excel dates prior to 01/01/1900 | Excel Discussion (Misc queries) |