Single quote in sheet name

[ankur]

Hi Experts,


I am actually wrting a program which will populate some of the cells
in sheet 1 with a formula which looks like .... "=John!A1+David!
A1+Smith!A1"

I am simply trying to add cell A1 values in various sheets which are
named as individual names.

The problem comes when some one's name is like O' RAMBO etc...

I think case the formula throws an error because formual does't
take '

Any workaround for this one?


Thanks A Lot in advance.

Regards
Ankur

 

[Ron Coderre]

Just do what Excel does if you reference that sheet from another sheet....
Double the single-quote, like this example:

ActiveCell.Formula = "='O'' RAMBO'!A1"

or...if you're reading the sheet name from the tab...
replace single-quotes with doubles...like this:
ActiveCell.Formula = "='" & Replace(Sheet4.Name, "'", "''") & "'!A1"

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Ron de Bruin]

Look at the formula if you create a link

='O'' RAMBO'!A6

See also
http://www.rondebruin.nl/linksum.htm

 

[Dave Peterson]

='O'' RAMBO'!A1+....

One way of getting the right syntax for this kind of stuff is to let excel do
the heaving lifting.

Select a cell on that troublesome sheet: O' Rambo
Edit|copy
Off to an unused cell on a different sheet in the same workbook
Edit|paste special|click Paste Link

Look at how excel builds the formula for you.

(Clean up that pasted cell when you're done.)