Using hyperlink function in IF conditional cell formula

[Mrock1]

I have just tied to use the hyperlink function in an if formula in a cell. Essentially the formula if condition is if there is no value in another cell then hyperlink to that cell (I use the # prefix ) and if there is a value then use that value. In simple terms if is:

=If($A$1=“”, hyperlink(“#$A$1”, “Click here”), $A$1)

I will admit that the cell reference is a calculation to find an adjacent cell value and reference the target cell using an Address, Index, Match combination, but the formula above is a simplified version

The problem is that if there is no value in the target cell, the hyperlink works just fine. However, if there is a value, then the value is displayed in the formula cell but clicking in that cell produces a hyperlink error “Can’t open the file”. Empirically this suggests that there is somehow a residual but corrupted hyperlink associated with the cell. Does anyone know why this will be happening?

The only way I can see around this problem is to have two hyperlink functions for each of the IF true and false conditions to explicitly define the hyperlink for both outcomes like:

=if($A$1=“”, hyperlink(“#$A$1”, “Click here”), hyperlink(“#$A$1”, $A$1))

Frankly this is probably most appropriate as I don’t want the user to be able to enter a value in that cell but take them to the target cell where the value should be entered. So I’ve worked around my problem but the question is still there and I’d appreciate if anyone can explain this behaviour.

Thanks again.

Max

 

[jasonb75]

The first one will work if there is a # prefix in A1, if there is not then you need to include it in the formula.

=IF($A$1="", HYPERLINK("#$A$1", "Click here"), "#"&$A$1)

Without the # prefix it assumes that the cell reference in A1 is the name of a file to be opened, not a cell in the same sheet.

 

[Mrock1]

I understand about the need the #i within the hyperlink argument, but the second reference to $A&1 is the false argument of the IF statement and that argument is a simple absolute cell reference, not a string so the “#” & would likely just result in a type definition error. So why would a # be required.

Don’t get me wrong though, I appreciate the feedback.

Thx
Max

 

[jasonb75]

that argument is a simple absolute cell reference, not a string

But the content of that cell is a string which is what the formula will be looking at.

 

[Mrock1]

Ok. I’ll give it a try tomorrow. Thx

 

[jasonb75]

I've just read your original question again and it looks like I may have reversed what you require.

In your first formula, only the middle part is valid as a hyperlink =If($A$1=“”, hyperlink(“#$A$1”, “Click here”), $A$1) with the rest, for all intents and purposes, being a regular formula.
When you use the hyperlink function the cell becomes the link rather than just that portion of the formula. Anything outside of the hyperlink function is treated as a link destination, not a text string to be used as a 'user friendly link name'.

I haven't tested this one but if I've read it correctly this time and you actually want the content of A1 to be displayed rather than used as the link destination, with an absolute reference to A1 always being the link destination then you would need to reverse the nesting in the formula. For example:-

=HYPERLINK("#$A$1",IF($A$1="","Click Here",$A$1))

 

[Mrock1]

Yes you have understood my intent to have the value of A1 if there is a value in A1 and have a hyperlink k to A1 whether or not there is a value in A1. While I find your explanation about the use of the hyperlink function I think I get the guist. So if I wanted to have the formula either display “Click here” or the value if there is a value in A1 and have the same hyperlink would it be accurate to use the IF statement with two different hyperlink functions for the true and false conditions, or would I need something a little more convoluted?

Or, does the formula you just suggested achieve that result - hyperlink jump to A1 in any case and either display the “Click here” or the value of A1?

Thanks in advance.

Max

 

[jasonb75]

Or, does the formula you just suggested achieve that result

It should, but as I said, I haven't tested it. You did mention that the example was a simplified version of your formula so I can't say for certain without seeing the proper formula, longer formulas can often require a different approach.

In theory, you would only need to use the formula with 2 hyperlink functions if both the target and the description would be different based on the condition, in reality it might be easier to use if the rest of the formula is too complex to nest in the way that I suggested.

While I find your explanation about the use of the hyperlink function I think I get the guist.

I didn't do a very good job of explaining it. Think of it as using the function with the second (optional) argument omitted, for example =HYPERLINK("#$A$1")
If the result of the condition is outside of the hyperlink function then that is how it will behave, with the result of the condition being both the target to jump to and the text to display. With no # prefix it would be seen as an external reference.

Hope that makes a bit more sense.

 

[Mrock1]

I do have one more complication. While in the above I have said that the target cell is A1, it is in fact the result of a search.

The cell I want the hyperlink formula in has an adjacent cell, a key field if you like. I need to search all the cels above rhr current cell for the first existence of they met field. Ie the first occurrence of the same value. Suppong the hyperlink cell is B100 -and the key field is in column A, I do this using an ISNUMBER(MATCH(A100,A$1:A99,0)) as a first IF condition. If This is true I want the second IF condition to achieve what I’ve already spoken about, but instead of $A$1, I would use the INDEX, combined with a COLUMN() function in an ADDRESS formula to identify the found row’s corresponding value instead of the simple $A$1. The secondary IF is the one containing the hyperlink.

IF there is a row above with the same value in column A then ima if the corresponding value in column B of that row is not “” then hyperlink to that cell (using the formula to determine the address, appending a # as required. IF the cell contains a value then display that value. So the question is can I include the hyperlink function in the secondary IF condition. And, if there is no previous row with the same key value in column A, then this is the first occurrence so display a “” without a hyperlink.

That’s probably as clear as mud so sorry if I’ve confused things more.

Max

 

[jasonb75]

I think I kinda get what you mean, I'm probably gonna mess it up first time though.

Could you update your profile / account details to show which version of excel you are using please (link in my signature block below). After you have selected one you will need to scroll to the bottom of the page and save the changes or it will not show up.