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 t...
- 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
Ah. I could see the value of it being dynamic if all my other parsing were dynamic. But you'll note that I have 12 elements in the string. The worksheet name only happens to be the last. I could search for the nth instance of the carat to make the formula dynamic for all the parsing from the middle, but the the string isn't ever going to change so I just didn't see the value of bothering (or making the formula longer).
I agree with your contention, which is for your exclusive use. For the sake of those who viewed this thread who may have a similar, but not exactly the same, situation to yours, my dynamic formula prevails. Anyway, I hope you somehow learned something from me.
Absolutely I learned something. And I agree, dynamic is preferred.