Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Function
In Range A1:B3 of WorkBook2.xls I have:
A B 1 From Excel FileName: -- Temp11 2 From Sheet Name: -- Bob 3 From Cell Address: -- A1 In Cell B5 =INDIRECT(B1&$B$2&"!"&B3) But currently B5 is showing #REF! Can someone spot my problem? TIA, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Function
Sorry, should have added that my file Temp11.xls IS OPEN!
A1 contains "This is a test", co my B5 should also, "Jim May" wrote in message : In Range A1:B3 of WorkBook2.xls I have: A B 1 From Excel FileName: -- Temp11 2 From Sheet Name: -- Bob 3 From Cell Address: -- A1 In Cell B5 =INDIRECT(B1&$B$2&"!"&B3) But currently B5 is showing #REF! Can someone spot my problem? TIA, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Function
Maybe...
=INDIRECT("'["&B1&"]"&B2&"'!"&B3) HTH Kostis Vezerides Jim May wrote: In Range A1:B3 of WorkBook2.xls I have: A B 1 From Excel FileName: -- Temp11 2 From Sheet Name: -- Bob 3 From Cell Address: -- A1 In Cell B5 =INDIRECT(B1&$B$2&"!"&B3) But currently B5 is showing #REF! Can someone spot my problem? TIA, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Function
Thank you VERY much;
Jim "vezerid" wrote in message oups.com: Maybe... =INDIRECT("'["&B1&"]"&B2&"'!"&B3) HTH Kostis Vezerides Jim May wrote: In Range A1:B3 of WorkBook2.xls I have: A B 1 From Excel FileName: -- Temp11 2 From Sheet Name: -- Bob 3 From Cell Address: -- A1 In Cell B5 =INDIRECT(B1&$B$2&"!"&B3) But currently B5 is showing #REF! Can someone spot my problem? TIA, |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Function
Kostis;
Studying this a bit further I'd like to better understand the breakdown of this Indirect() function. It seems that the first normal-looking part is: =Indirect(??? B3) << the B3 Next the & in front of the B3 concatenates the mystery part to the B3 with =Indirect(??? & B3) Then Everything in the ??? part is within double quotes =Indirect("???"& B3) then Only on the right-side end is the ! giving: =Indirect("??? !"& B3) then the ??? is wrapped in a single quote, like so: =Indirect(" '???'!"& B3), then ??? becomes [????" At this point I'm getting uncertain of what's going on. Can you assist me in some way? Thanks in advance, "vezerid" wrote in message oups.com: Maybe... =INDIRECT("'["&B1&"]"&B2&"'!"&B3) HTH Kostis Vezerides Jim May wrote: In Range A1:B3 of WorkBook2.xls I have: A B 1 From Excel FileName: -- Temp11 2 From Sheet Name: -- Bob 3 From Cell Address: -- A1 In Cell B5 =INDIRECT(B1&$B$2&"!"&B3) But currently B5 is showing #REF! Can someone spot my problem? TIA, |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Function
JMay wrote...
.... =Indirect(" '???'!"& B3), then ??? becomes [????" At this point I'm getting uncertain of what's going on. Can you assist me in some way? What's inside INDIRECT needs to be a syntactically valid external reference as text. Try creating literal external references by pressing = then [Ctrl]+[F6] to switch to another workbook, press an arrow key then [Enter]. You should be back in workbook in which you typed the = with a simple formula showing an external reference. In short, the filename part will appear inside square brackets. The worksheet name will follow immediately after the right square bracket. If there are any spaces, hyphens or characters other than just letters, numerals and underscores, the bracketted workbook name and worksheet name together will appear within single quotes. Immediately after this there'll be an exclamation point which serves as a separator between the [workbook]worksheet name and the cell address, and after it there'll be the cell address. While there are times that Excel doesn't need the single quotes, it always accepts them, much the same as you can enter whole numbers like five as 5.000, so it's always safest to include single quotes when constructing text references. As for the square brackets, there has to be some way of distinguishing the workbook name from the worksheet name. In your original example, B1&B2 == "Temp11Bob". How would Excel be able to tell this wasn't a worksheet named Temp11Bob? So something needs to separate Temp11 and Bob. Microsoft chose to use square brackets (unfortunately, since square brackets were and are legal filename characters in all Mac OS versions and in all 32-bit versions of Windows) around the base filename. It's a necessary part of the syntax. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Function
If you add spaces between the items, they may be easier to read and
understand: =INDIRECT("'[" & B1 & "]" & B2 & "'!" & B3) First, a single quote and open square bracket are added: '[ Then, the file name in cell B1: Temp11 Then a bracket to end the file name: ] Then, the sheet name in cell B2: Bob Then a single quote and apostrophe to end the file and sheet name: !' Finally, the cell reference in cell B3: A1 In your example, the single quotes aren't necessary, because your file name and sheet name don't contain space characters. However, it's a good idea to include them, so the formula will work even if space characters are included. JMay wrote: Kostis; Studying this a bit further I'd like to better understand the breakdown of this Indirect() function. It seems that the first normal-looking part is: =Indirect(??? B3) << the B3 Next the & in front of the B3 concatenates the mystery part to the B3 with =Indirect(??? & B3) Then Everything in the ??? part is within double quotes =Indirect("???"& B3) then Only on the right-side end is the ! giving: =Indirect("??? !"& B3) then the ??? is wrapped in a single quote, like so: =Indirect(" '???'!"& B3), then ??? becomes [????" At this point I'm getting uncertain of what's going on. Can you assist me in some way? Thanks in advance, "vezerid" wrote in message oups.com: Maybe... =INDIRECT("'["&B1&"]"&B2&"'!"&B3) HTH Kostis Vezerides Jim May wrote: In Range A1:B3 of WorkBook2.xls I have: A B 1 From Excel FileName: -- Temp11 2 From Sheet Name: -- Bob 3 From Cell Address: -- A1 In Cell B5 =INDIRECT(B1&$B$2&"!"&B3) But currently B5 is showing #REF! Can someone spot my problem? TIA, -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Function
Debra, Thanks - yes, you'r right my formula does work WITHOUT the
single-quotes(below); =INDIRECT("["&B1&"]"&B2&"!"&B3) Harlan mentioned that what's inside the Indirect() must be TEXT -- Just toying with things in a blank cell (C3) I entered [ and then in an adjacent cell entered =ISTEXT(C3) and it produced TRUE So why (above) should I have to enter the "[" if [ is already text..?? It would seemthat I cound enter like so, =Indirect([ &B1& ] &B2&"!"&B3) << Also, the ! characters when tested ISTEXT = True -- so why within the " "'s? I'm sure I'm missing something here, which you can enlighten me on. Appreciate you assistance; Jim "Debra Dalgleish" wrote in message : If you add spaces between the items, they may be easier to read and understand: =INDIRECT("'[" & B1 & "]" & B2 & "'!" & B3) First, a single quote and open square bracket are added: '[ Then, the file name in cell B1: Temp11 Then a bracket to end the file name: ] Then, the sheet name in cell B2: Bob Then a single quote and apostrophe to end the file and sheet name: !' Finally, the cell reference in cell B3: A1 In your example, the single quotes aren't necessary, because your file name and sheet name don't contain space characters. However, it's a good idea to include them, so the formula will work even if space characters are included. JMay wrote: Kostis; Studying this a bit further I'd like to better understand the breakdown of this Indirect() function. It seems that the first normal-looking part is: =Indirect(??? B3) << the B3 Next the & in front of the B3 concatenates the mystery part to the B3 with =Indirect(??? & B3) Then Everything in the ??? part is within double quotes =Indirect("???"& B3) then Only on the right-side end is the ! giving: =Indirect("??? !"& B3) then the ??? is wrapped in a single quote, like so: =Indirect(" '???'!"& B3), then ??? becomes [????" At this point I'm getting uncertain of what's going on. Can you assist me in some way? Thanks in advance, "vezerid" wrote in message oups.com: Maybe... =INDIRECT("'["&B1&"]"&B2&"'!"&B3) HTH Kostis Vezerides Jim May wrote: In Range A1:B3 of WorkBook2.xls I have: A B 1 From Excel FileName: -- Temp11 2 From Sheet Name: -- Bob 3 From Cell Address: -- A1 In Cell B5 =INDIRECT(B1&$B$2&"!"&B3) But currently B5 is showing #REF! Can someone spot my problem? TIA, -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Function
JMay wrote...
.... Harlan mentioned that what's inside the Indirect() must be TEXT -- Just toying with things in a blank cell (C3) I entered [ and then in an adjacent cell entered =ISTEXT(C3) and it produced TRUE So why (above) should I have to enter the "[" if [ is already text..?? .... INDIRECT's argument needs to *evaluate* as text. If you entered [ in a cell, then referred to that cell, you wouldn't need double quotes around the cell reference. Putting it another way, =ISTEXT([) is a syntax error. Also, it's C3 that's being evaluated in your formula, not [. The point is that you can enter text strings into cells without double quotes, then you can refer to them by cell address and Excel will evaluate the cell's contents as if they had been entered directly in the formula *within* double quotes. But if you actually do enter a string constant in a formula, it needs to be within double quotes. If not, there's too much ambiguity for Excel. Consider a different example. FOO and BAR are simple 3-letter strings, but the formula =FOO&BAR won't return FOOBAR unless FOO and BAR are defined names that refer to ordered complementary substrings of FOOBAR. If either FOO or BAR were undefined, the formula would return #NAME?. Something similar is going on with [, ] and !. They're each syntactic tokens that Excel uses for specific purposes when evaluating formulas. That so, Excel will not treat them only as such tokens when they appear on their own without enclosing double quotes. If you want Excel to treat them as string constants, you have to tell Excel to do so by emclosing them in double quotes. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Function
Harlan Grove wrote:
.... . . . That so, Excel will not treat them only as such tokens when they appear on their own without enclosing double quotes. . . . .... @#$% typos! Delete the not. Sentence should be That so, Excel will treat them only as such tokens when they appear on their own without enclosing double quotes. . . . |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Function
Thank you Harland, I better understand the subject now,
I deeply respect your input to this and other groups. Jim "Harlan Grove" wrote in message oups.com: Harlan Grove wrote: ... . . . That so, Excel will not treat them only as such tokens when they appear on their own without enclosing double quotes. . . . ... @#$% typos! Delete the not. Sentence should be That so, Excel will treat them only as such tokens when they appear on their own without enclosing double quotes. . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why is the INDIRECT function volatile? | Excel Worksheet Functions | |||
consolidate data using 3d function AND indirect | Excel Worksheet Functions | |||
INDIRECT function inside AND function | Excel Worksheet Functions | |||
Using INDIRECT in INDEX(LINEST.. ) function | Excel Worksheet Functions | |||
include INDIRECT function into SUMPRODUCT formula | Excel Worksheet Functions |