Indirect reference and worksheets with single quote in the name

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.

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

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
>

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

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
>

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

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" <tedthompson61@discussions.microsoft.com> wrote
in message
news:24E80360-FA6E-4054-8E6B-E7A86DB16CA8@microsoft.com...
> 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
>> >

A2:=INDIRECT("'"&SUBSTITUTE(A1,"'","''")&"'!B2")

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


ted_thompson61 wrote:
> 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
>>>

That did it. Thanks Chip, Jon and Dave!!!



"Jon Peltier" wrote:

> A2:=INDIRECT("'"&SUBSTITUTE(A1,"'","''")&"'!B2")
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> ted_thompson61 wrote:
> > 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
> >>>
>