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]

Using a simplified example, is this what you're looking for?

Book1
	A	B
1
2	a	1
3	b	2
4	c	3
5	d	4
6	a	1
7	b	2
8	c	3
9	d	4
10	e
Sheet1

Cell Formulas
Range	Formula
B6:B10	B6	=IFERROR(HYPERLINK("#B"&MATCH(A6,$A$1:$A5,0),INDEX($B$2:$B5,MATCH(A6,$A$2:$A5,0))),"")

 

[Mrock1]

FYI I use O375

Your formula is a great idea. I ended up creating a nested if condition with 2 explicit hyperlink functions for the true and false conditions, but hadn’t thought to use an iferror. My formula essentially uses the first if condition to result in a “” if the record in column A was found. If it is found then I do another if to make the same hyperlink jump location but display “Click get to update the value on row n” or the value that is on row n. But looking at your construct it may be a way to shorten tbe formula. I can’t look at it u til I get to work tomorrow but will definitely see if I can use your approach to achieve the outcome I want in a shorter formula.

Thanks for the help.

Max

 

[jasonb75]

FYI I use O375

I'm gonna guess that the 7 is a typo and it should be 365. If you do as I suggested earlier then it shows under your user name on the left side of your posts so that anyone helping you can see without having to ask you in every thread.
While the formula that I've suggested here is good for any version of excel back to 2007 it is often the case that additional functions in newer versions of excel will allow for better answers. If people don't know which version you use they may assume incorrectly and not necessarily provide what could be the best answer for your actual version.