Hyperlink help

[jrisebo]

I have a cell that returns 3 values, either its 'WALL', 'FTG', or OK. Is there anyway to do a hyperlink that will have a if statement? The cell already has a formula in it, so not sure how to also do a hyperlink with an if statement.
Thanks
Jim

 

[VoG]

I think that would require VBA not a formula. where should the "hyperlink" go to for each condition? Which cell contains the drop-down?

 

[jrisebo]

ok, my cell is J44 on the 'Input' tab. Its formula is '=IF(hwwa1<>0,IF(AND(II.stability!H53="OK",II.stability!H13="OK"),"OK",IF(II.stability!H13<>"OK","WALL","FTG")),"NA")'
so it can return WALL, FTG, or OK. If its WALL i want the hpyerlink to goto 'II.Stability' tab cell H13, if its FTG I want the hyperlink to goto "II.Stability' tab cell H53, and if OK it can goto either cell, really no reason to link if ok. I use the links so the user can click to that tab without having to scroll across the bottom to find the right tab and cell. This make sense?
Thanks

 

[VoG]

Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Calculate()
Select Case Range("J44").Value
    Case "WALL", "FTG": Application.Goto Sheets("II.Stability").Range("H53"), True
End Select
End Sub

 

[jrisebo]

It doenst do anything. Should I expect cell J44 to endup with a clickable value?

 

[VoG]

Make sure that macros are enabled.

 

[jrisebo]

ok, it works. I guess thats not what I was looking for, I wanted the cell to be like a hyperlink, just going to different locations depending on what is shown, and then you click it and it takes you to the dependent cell.

 

[jrisebo]

also, when it says FTG, it would goto H53, and if it says WALL it would goto H13, and OK, it doenst have to go anywhere. I dont see in the code how it goes to H13?