3

I'm attempting to make a formula that will toggle a hyperlink on or off depending on the argument. Basically, I want to make it argue the following:

If there is text in (Tab A, Cell A), then create a hyperlink with the title (Text of Tab A, Cell A) that links to (Tab B, Cell A). If no text is present in (Tab A, Cell A), then just put the text "Not in Use" with no hyperlink.

I was attempting this formula, but it's not quite working. As long as there is text in SETUP!D39, it displays the text and activates the hyperlink, but the text is not blue or underlined. If there is no text in SETUP!D39, then it displays the text (Not In Use) properly, but the hyperlink is still there and gives the error "Cannot open the specified file". I'm pretty sure it's just a syntax error in my formula, but I can't figure it out:

=IF(ISTEXT(SETUP!D39),HYPERLINK("#'DEDICATED FUNDS (10)'!A1", (SETUP!D39)), "(NOT IN USE)")

So my questions are:

  1. Any idea why the hyperlink is not turning blue and underlining?
  2. Any suggestions on why the hyperlink still exists in the second argument?

Any help is greatly appreciated.

0

1 Answer 1

0

In Excel it's pretty much "Once a Hyperlink, always a Hyperlink". You just need to take care that you have a hyperlink for the FALSE part of the IF statement as well, so the link has somewhere valid to jump to. One possibility is to create a hyperlink to the cell that is just being clicked.

If the formula lives in the file Book1.xlsx in Sheet1!A1, then you can use something like

=IF(ISTEXT(SETUP!D39),HYPERLINK("#'DEDICATED FUNDS (10)'!A1", SETUP!D39), Hyperlink([Book1.xlsx]Sheet1!A1,"(NOT IN USE)"))

Adjust the file name and cell reference to your situation.

2
  • This is what I suspected, but I couldn't find any documentation. I couldn't figure out why referencing a hyperlink friendly name to a cell value doesn't make it look like a hyperlink though (blue, underlined). Any idea on that? May 19, 2014 at 12:31
  • 1
    Just use the hyperlink cell style. You can then use conditional formatting to remove the color and underline when the cell shows '(NOT IN USE)'. Excel does not apply the hyperlink formatting unless it recognizes that the cell contains a hyperlink. Nesting the Hyperlink() function within an IF() function obscures this recognition.
    – teylyn
    May 19, 2014 at 20:34

Not the answer you're looking for? Browse other questions tagged .