1

I have a spreadsheet which contains a hyperlink based on the contents of another cell. I need the hyperlink to be based on the display (formatted) value rather than the data value of the source cell. Is this possible without resorting to VBA? Example:

Actual values:

| A |          B                | 
| 1 | =HYPERLINK("../Form_"&A6) |
| 2 | =HYPERLINK("../Form_"&A7) |
| 3 | =HYPERLINK("../Form_"&A8) |

Displayed As:

Column A custom format: "Form_"000

|    A     |      B      | 
| Form_001 | ../Form_001 |
| Form_002 | ../Form_002 |
| Form_003 | ../Form_003 |

Functional Value:

| A |          B          | 
| 1 | file:///../Form_001 |
| 2 | file:///../Form_002 |
| 3 | file:///../Form_003 |
  • Is there some fixed format to the contents of Column A and the form names, or is this a generic question? You could do something using TEXT() if you can avoid the "m" in Form (because TEXT() will think it's a month indicator), but that's not a general solution... – Rhys Gibson Dec 6 '11 at 21:58
  • e.g. =HYPERLINK("../"&TEXT(A2,"For_000#")) works, but =HYPERLINK("../"&TEXT(A2,"Form_000#")) doesn't – Rhys Gibson Dec 6 '11 at 22:28
  • @RhysGibson ahhh, that got me close enough: =HYPERLINK("X:/Path/to/Form_"&TEXT(A2,"000"). Post as an answer and get your just dues :) – matt wilkie Dec 6 '11 at 23:20
2

Is there some fixed format to the contents of Column A and the form names, or is this a generic question?

You could do something using TEXT() if you can avoid the "m" in "Form" (because TEXT() will think it's a month indicator), but that's not a general solution.

For example: =HYPERLINK("../"&TEXT(A2,"For_000#")) works, but =HYPERLINK("../"&TEXT(A2,"Form_000#")) doesn't.

However if you can add the extra bits you need outside of the &text construct you can use whatever characters you like:

=HYPERLINK("../Form_"&TEXT(A2,"000"))

You can use the standard formatting codes in the "..." part, e.g. "$0.00", "yyyy/mm/dd".

| improve this answer | |
2

I don't see a function for returning a cell as its formatted value. Even though you prefer a non-VBA solution, it is a simple, straight forward function:

Function AsFormatted(v As Variant) As String
  AsFormatted = v.Text
End Function
| improve this answer | |
0

Unless I am misunderstanding things, you simply want to change the simple 1-3 digit number value in column A to be preceded by "Form_" and to always use three digits to present the number.

The formatting string that will do that is:

\F\o\r\m_000

Note the "\" before each of the first five characters. That ensures Excel treats them as text, not as something like a month indicator, or some other bizarre surprise. Then the three zeros at the end will be understood as requiring a number to have the full three characters, so "82" becomes "082" and so on.

But of course, formatting the cells to have that format won't help you get that output to use in a text string! However, you can use the TEXT() function WITH that string specified to do exactly that. Then your HYPERLINK() function will have all the string material it requires to form the desired hyperlink.

Please note that:

"Form_"000

will also work in cell formating as the double quotes around the text makes Excel realize not to see, sasy, the "m" as indicating a month, but in the TEXT() formula, would need doubled double quotes where you see the single double quotes now:

""Form_""000

because the formula itself is seeing the single double quotes as setting off the entire formatting string rather than indicating text to put in the format. But I find the extra double quoting obnoxious and occasionally bizarre in what ends up working so I would use the first formatting string I gave since it will always work and requires nothing more obnoxious... oh... than all those \'s.

Hope that's what you were really looking for!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.