Hyperlink not working because I have single quotes in the Workbook name

[kristiguerrero]

I'm trying to get the hyperlink function to link to another worksheet within my excel file. This is what I have right now;

=HYPERLINK("#'" & D1 & "'!" & B4,"click me")

where D1 contains the workbook name and B4 has the cell address. I've tried it with another worksheet in my file and it's fine, so I know the formula works, but the name of the worksheet I want to have the link go to already contains single quotations and I'm sure that's why it's not working. My co-worker created the workbook and has the worksheet named for the dimensions of what we are designing.

Example: 18' deck

So single quotes are needed.

Is there any go around to have this work?


Also, I tried just linking it through the hyperlink dialog box, but if I save and close the workbook and open it again it gives me the "Reference not valid" error still.

Any help is much appreciated.

 

[Lee.]

Can you not use a macro to select the sheet?

Sub SheetSelect()
Dim strName As String
 
strName = Range("D1")
Sheets(strName).Select


End Sub