Indirect() with sheet names containing apostrophes

[Utterly Bamboozled]

I've just discovered that when a sheet name contains an apostrophe (e.g. Adrian O'Shea), a reference to this sheet will replace the apostrophe with vertical commas (e.g. 'Adrian O''Shea'!A1).

This results in #REF errors when using the Indirect() function, as it tells Excel to look for 'Adrian O'Shea'!A1 instead of 'Adrian O''Shea'!A1

Does anyone know how to get around this?

Cheers,

UB

 

[T. Valko]

I've just discovered that when a sheet name contains an apostrophe (e.g. Adrian O'Shea), a reference to this sheet will replace the apostrophe with vertical commas (e.g. 'Adrian O''Shea'!A1).

This results in #REF errors when using the Indirect() function, as it tells Excel to look for 'Adrian O'Shea'!A1 instead of 'Adrian O''Shea'!A1

Does anyone know how to get around this?

Cheers,

UB

I've never encountered that before.

The easy fix is to remove the apostrophe from the sheet name: Adrian OShea

Seems like MS should have made that an illegal character in sheet names.

 

[shg]

See http://support.microsoft.com/kb/107468.

The easy answer is, don't use them in sheet names.

Note that this may also be the source of the problem in your other thread.

 

[T. Valko]

See http://support.microsoft.com/kb/107468.

The easy answer is, don't use them in sheet names.

Note that this may also be the source of the problem in your other thread.

What a PITA!

A1 = Adrian O'Shea

=INDIRECT("'"&SUBSTITUTE(A1,"'","''")&"'!A1")

Resolves to:

='Adrian O'Shea'!A1

The easy answer is, don't use them in sheet names.

Yep!

 

[Jonmo1]

You can use substitute in the indirect to replace an apostrophe with 2 apostrophes

=INDIRECT("'"&SUBSTITUTE(C1,"'","''")&"'!A1")
C1 = Adrian O'Shea

in the substitute, it's
quote|apostrophe|quote
and
quote|apostrophe|apostrophe|quote

I'll give that a third....don't use apostrophe's in Sheet Names.

 

[Utterly Bamboozled]

Much appreciated, apostrophes are OUT!

Regards,

UB

 

[T. Valko]

Much appreciated, apostrophes are OUT!

Regards,

UB

You made the right decision!