Excel conditional Hyperlink outputs the Hyperlink for both conditions

Question

I think I must be going mad here, what I want to do is to write a simple Excel formula that performs an IF test, and if the result is false I want to hyperlink some text, if it is true I want to display some text which is not hyperlinked.

So for example -

=IF(1=1,"1",HYPERLINK("2"))

this returns 1 as you would expect, but the 1 is hyperlinked - I only want to hyperlink if the value is 2.

Surely this is possible? Thanks in advance for any advice!

Answer 1

Update:

Oh, I was wrong. I checked into it and this happens because when Excel sees the 'Hyperlink' function in a cell it turns the entire cell into a 'followable' cell so that links will work.

The work around to avoiding the error message "The address of the site is not valid" (which occurs when the hyperlink is turned on for the cell but the link has nowhere to go)... is to give excel a link that goes nowhere by referencing the cell address of the formula.

Example to paste into cell A1:

=IF(1=2,HYPERLINK("#"&CELL("address",A1),"1"),HYPERLINK("C:\","2"))