Try the following:
=INDIRECT("'"&SUBSTITUTE(A1,"'","''")&"'!B1")

pay very close attention to the single and double quote marks.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"ted_thompson61" wrote
in message
...
Just to be clear the final result should be something like
this.

A1:ABC Int'l

A2:=INDIRECT(A1&"!B2")

Something like this should look up the value in cell B2 on
Sheet ABC Int'l.

The formula should also work for other names in cell A1 like:
ABC or ABC 123.





"ted_thompson61" wrote:

Yes, I finally got it to work by putting 2 single quotes where
it looked like
there was a double quote and that worked, BUT

If I have the following text in cell A1:ABC Int'l

How can I build a string that will reference the sheet named
ABC Int'l?

(This is the lone troublesome case that I have, all the rest
of the sheets
have one or two names and no special characters that cause
problems.)

I can get it to work like you said by building the string
manually in a cell
and using '' where the ' is in Int'l, but can't seem to do it
automatically
from a cell containing the sheet name.

Thanks for all the good help!

--Ted



"Dave Peterson" wrote:

How about putting this in A1:

="'int''l'!a1"
(that worked, too.)

That first apostrophe is used by excel to force it to treat
the value as text.

If you look at the value in the cell--not in the formula
bar, then you should
only see one apostrophe at the far left.

ted_thompson61 wrote:

That did not seem to work. Did you get that to work?

And yes, if it were up to me, I would not use the ' in a
sheet name. I want
to have a link to a table in a web site so I can
automatically update my
data. The name they are using has Int'l in the name. I
need to look-up
information on my worksheet for the Name Int'l

I suppose I could go another level and have an indirect
reference to a sheet
after a table look-up on the name

"Dave Peterson" wrote:

Try this in A1:

''Int''l'!A1

Maybe a good reason to avoid apostrophes in the
worksheet names!


ted_thompson61 wrote:

I can't seem to use the indirect function to lookup
data in another sheet if
the sheet name contains a single quote.

Sheet1 - I have in cell A1: Sheet2!A1
In cell A2 I have: =INDIRECT(A1)

If I change the name of Sheet2 to Int'l and change the
contents of A1 to
Int'l!A1

then it no longer works.

What can you do when the sheet name has a single
quote? I am going to be
referencing data from a web site and I want the names
of the sheets to be
able to be referenced just by the name of the data. I
have no control over
this data and so it will have the ' in it.

--

Dave Peterson


--

Dave Peterson