| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Indirect reference and worksheets with single quote in the name
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Indirect reference and worksheets with single quote in the nam
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Indirect reference and worksheets with single quote in the nam
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Indirect reference and worksheets with single quote in the nam
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Indirect reference and worksheets with single quote in the nam
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Indirect reference and worksheets with single quote in the nam
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Indirect reference and worksheets with single quote in the nam
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Indirect reference and worksheets with single quote in the nam
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup and using single quote | Excel Worksheet Functions | |||
| Single Quote use in cell reference | Excel Worksheet Functions | |||
| Remove single quote | Excel Worksheet Functions | |||
| indirect cell reference using copies of worksheets in same workboo | Excel Worksheet Functions | |||
| Summarizing Data across Worksheets using a single reference | Excel Worksheet Functions | |||
Linear Mode
