# Using Indirects in Excel Involving Quotation Marks

Is it possible to use indirects on texts with Quotation Marks?

Here's what I have right now.

=IF(\$A9="TOTAL INT'L",'TOTAL INT''L'!\$Q\$43,INDIRECT("'"&\$A9&"'!Q43"))

I'm just wondering if there's a better way to do this.

Thanks.
LVL 1
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IT Services ConsultantCommented:
I'm just wondering if there's a better way to do this.

Explaining what you are attempting to achieve with/without Quotes & the INDIRECT() function may be helpful! :)

What is in cells [A9] & [Q43]?
Author Commented:
A9 Refers to the name of the Work-sheet and Q43 refers just to a particular datapoint in the sheet.
IT Services ConsultantCommented:
The name of the worksheet [A9] needs to be encapsulated within single quotes (') if there there is a space character in the worksheet name.  No space within the name & the name can be used without encapsulation.

For example,

Also adding to confusion, is that I think you are using the explicit text TOTAL INT'L as the name of the worksheet.  If so, the apostrophe will need to be "doubled-up", as you have in the earlier clause of the IF() function.

That is, to point to cell A1 or the [TOTAL INT'L] worksheet you will need to use:
='TOTAL INT''L'!A1

(Single Apostrophe)TOTAL INT(Single Apostrophe)(Single Apostrophe)L(Single Apostrophe)!A1

Is the apostrophe in the value in cell [A9] (where the name of the worksheet is stored) "doubled-up" in this way?

If not, then the INDIRECT() function statement will need to replace all single apostrophes with a set of two.

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thank you.  That works out great.
IT Services ConsultantCommented:
You're welcome.

Are you OK with replacing single apostrophes with doubles?

If you have not explicitly changed cell [A9], then look at the SUBSTITUTE() in-cell function...

=SUBSTITUTE(A9, "X", "XX")

I have used X to represent an apostrophe, otherwise that may have been very confusing to read!
Author Commented:
That works fine with me.