Error when using Indirect with Tab name "Sheet+"

[viking2]

The following formula evaluates correctly: =INDIRECT(A2&"!A1")
However, I get an error with this one: =INDIRECT(A3&"!A1")

Where A2 has the value Sheet2
and
Where A3 has the value Sheet+

Why can't I use a Sheet name = "Sheet+"?

(see attached example)

[Pete_UK]

You need to put apostrophes around the sheet name if it contains certain special characters (like a space), so you should use this in C2:

=INDIRECT("'"&A2&"'!A1")

then you can copy into C3. Note that there is an apostrophe just before the !.

Hope this helps.

Pete

[viking2]

Thanks Pete, that worked! However, I do not understand the purposes of all the quotes and additional ampersand. Could you please explain why those are needed?

[Pete_UK]

If you had a direct link to a cell in that sheet, you would need to use this:

='Sheet+'!A1

Note the apostrophes either side of the sheet name.

When you use INDIRECT this converts a text string which represents a cell (or range) reference into the actual reference so that Excel can make use of it. So, your string within the brackets of the function should result in the textual part of the formula shown above. Quotes are used either side of the first apostrophe as you need that to be text and to join it on to whatever is in cell A2, and the second apostrophe can be contained within the second string.

Hope this helps.

Pete

[viking2]

Thanks, I almost got it!
I am a little confused as I have used double-quotes (") and apostrophes (') interchangeably to indicate strings in other scripting languages.
Is it correct then that in Excel, only double-quotes (") is used to indicate strings?
What is then the meaning of the apostrophes (') around 'Sheet+' ?

[Pete_UK]

Yes, double-quotes are used to indicate strings in Excel.

Suppose you have sheets named like WK1, WK2, WK3 etc, for data occurring in different week numbers, or named 1, 2, 3 etc. for days of the month. Excel could get confused with references to the first set as those could also be references to actual cells. Similarly, reference to the days of the month could also be confused with actual numbers. To avoid the confusion, sheet names like those (and others containing spaces and other special characters) must be enclosed with apostrophes. That applies to direct references, so if you want to use INDIRECT, where the sheet names are in a cell, you have to follow the same rules. Even if you don't actually need the apostrophes (like references to Sheet1, Sheet2 etc.), it does no harm to include them, so you may as well use them all the time with INDIRECT.

Hope this helps.

Pete

P.S. By the way, if that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[viking2]

Got it, Thanks!