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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

[ fanpages ]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]?
mattfmillerAuthor Commented:
A9 Refers to the name of the Work-sheet and Q43 refers just to a particular datapoint in the sheet.
[ fanpages ]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,

Trading!Q43 is valid as is 'Trading for 2015'!Q43
'Trading'!Q43 will be valid too, but Trading for 2015!Q43 is not.

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:

(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

Your issues matter to us.

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

Start your 7-day free trial
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

mattfmillerAuthor Commented:
Thank you.  That works out great.
[ fanpages ]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...


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

Thank you for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.