Hyperlink nested in an IF function

[SAXT]

Is it possible to create an IF argument in a cell and still hyperlink it to another cell in the same workbook?

I see the main problem being: that the value of the cell will change depending on the outcome - can this cell still have a hyperlink even if the value changes, and if so how?

 

[hatman]

Try using the Hyperlink() function. example: =IF(A115=TRUE,HYPERLINK("#a51","link1"),HYPERLINK("#b51","link2"))

 

[Chippy]

I think you can do this by using the HYPERLINK function.

In my example below I check cell G8 to see what it contains.
If it contains the number 1

IF(G8=1

then it displays the number 1 as a Hyperlink

HYPERLINK("[Book1]Sheet1!A1",1)

to cell A1.
Otherwise it displays the number 2 as a Hyperlink to cell B1

HYPERLINK("[Book1]Sheet1!B1",2)

NB you have to have the full hyperlink to make this function work (ie [workbookname]Sheetname!cellref, even if you are making a reference the same sheet.

=IF(G8=1,HYPERLINK("[Book1]Sheet1!A1",1),HYPERLINK("[Book1]Sheet1!B1",2))

 

[SAXT]

This is really bugging me as it looks possible, but just doesn't seem to work!

I have a sheet called Overview and a sheet called Test. I want the cell in Overview to look at S14 in Test and return a 'Yes' if it contains the text 'Statisfactory' (part of a data validation drop-down box), and a 'No' if it doesn't - I then want the cell to have a hyperlink to cell R14 in the Test worksheet regardless of the outcome

If I try:

=IF(Test!S14="Satisfactory",HYPERLINK("Yes",Test!R14),HYPERLINK("No",Test!R14))

I get a pop up box telling me it can't open open the specified file.

If I try:

=IF(Test!S14="Satisfactory",HYPERLINK(Test!R14,"Yes"),HYPERLINK(Test!R14, "No"))

The pointer changes to the little hand, but clicking does nothing....

Any Ideas?

 

[hatman]

Yup, use the second syntax, BUT you need to enclose the address in quotes (look at both my and Chippy's examples). If you will not be specifying the ENTIRE qualified address, as Chippy recommends, you need to use a # before the address, as in my example... so you end up with:

=IF(Test!S14="Satisfactory",HYPERLINK("#Test!R14","Yes"),HYPERLINK("#Test!R14", "No"))

 

[SAXT]

Fantastic!

Works a charm.

Thank you both so much for your help - has saved me pulling more of my hair out!