Feb 20 2019 03:43 PM
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)),"")
Feb 20 2019 07:55 PM
Feb 20 2019 08:04 PM
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.
Feb 20 2019 09:50 PM
SolutionFeb 21 2019 03:54 AM
Thanks Twifoo.
That's a clever implementation substituting the carat for the pipe to get the location of the last carat. But I'm wondering what does it get me beyond using the MID function to parse out the worksheet name? Just inserting the single quotes into my existing formula is shorter. Am I missing something?
PS. After your first post I tried using single quotes, but I put the second one after the exclamation mark instead of before. Doh!
This works too:
=HYPERLINK("#'"&MID($A12,FIND(CHAR(1),SUBSTITUTE($A12,"^",CHAR(1),11))+1,31)&"'!A1",MID($A12,FIND(CHAR(1),SUBSTITUTE($A12,"^",CHAR(1),11))+1,31))
Feb 21 2019 04:10 AM
Feb 21 2019 04:53 AM
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).
Feb 21 2019 05:49 AM
Feb 21 2019 06:18 AM
Absolutely I learned something. And I agree, dynamic is preferred.
Feb 20 2019 09:50 PM
Solution