Forum Discussion
Solved
Issue with hyperlink formula when a space character is in the destination worksheet name
I'm using the following formula to create a hyperlink to another worksheet in the same workbook. The formula parses out the destination worksheet from another cell (A12 in the example below) using the carat delimiter. The formula works perfectly as long as the destination worksheet does not have any space characters in the worksheet name. Once a space is added in a worksheet name then the formula fails (with "Reference isn't valid."). I've tried escaping the double quotes in the formula and I've tried substituting the space character with CHAR(32), but either that's wrong or I'm screwing up the implementation. I'd appreciate any help in fixing the formula.
Thanks in advance,
Andrew
=IFERROR(HYPERLINK("#"&MID($A12,FIND(CHAR(1),SUBSTITUTE($A12,"^",CHAR(1),11))+1,50)&"!a1",MID($A12,FIND(CHAR(1),SUBSTITUTE($A12,"^",CHAR(1),11))+1,50)),"")
- Hi Andrew,
Assuming your destination cell is A1, your hyperlink formula is:
=HYPERLINK("#'"&RIGHT(A12,
LEN(A12)-FIND("|",SUBSTITUTE(A12,"^","|",
LEN(A12)-LEN(SUBSTITUTE(A12,"^","")))))&"'!A1","Link")
To be safe, always enclose the Sheet Name in single quotes (') whether or not such Name includes a space.
Cheers!
Twifoo
- Hello Andrew,
Please specify the value of A12 and the destination of your hyperlink. Note that the Sheet Name must be enclosed in single quotes (') whenever it includes a space.A12 is a formula. Below is the value:
Power^Smart-UPS - 6000VA - 208V to 208 - 4 OUTPUT / 1 INPUT - 6 RU^Cyberpower ^OL6KRT3UTAA^1^2014^8^2022^5652^0.03^7160^SH 110 Main
The destination worksheet is:
SH 110 Main
Thanks.
- Hi Andrew,
Assuming your destination cell is A1, your hyperlink formula is:
=HYPERLINK("#'"&RIGHT(A12,
LEN(A12)-FIND("|",SUBSTITUTE(A12,"^","|",
LEN(A12)-LEN(SUBSTITUTE(A12,"^","")))))&"'!A1","Link")
To be safe, always enclose the Sheet Name in single quotes (') whether or not such Name includes a space.
Cheers!
Twifoo
