Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 |
share|improve this question
    
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 Answers 2

up vote 2 down vote accepted

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".

share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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