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,

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,

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,

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,

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,

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.

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

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

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.

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

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