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