11
ColA                  Col B              ColC
Project A            A.pdf               A.pdf
Project B                                No Link

I have a list of project records. One column (e.g., B) may have a reference to a pdf file. If I create a formula 

=if(isblank(B2),"No Link",hyperlink(folderURL + B2),B2)

What I want is to place a hyperlink on only those rows where column B contains a pdf reference, and insert text "No Link". I get the proper text values, but in all rows have the hyperlnk default appearance and behavior -- Excel seems to be creating "phantom links" out of the "No Link" cells.

enter image description here

Clicking on the "No Link" value in Col C activates an attempt to open the file listed in Col B, which returns a No file found error.

Ellesa
  • 11,185
Bill
  • 111

3 Answers3

13

It's unclear if this is a bug; but Excel seems to be making the entire cell clickable just because there's a HYPERLINK function in the formula in order for it to work. Right-clicking on the cell & selecting Remove Hyperlink does not seem to work either.

I can't seem to find a non-VBA solution to prevent Excel from creating those "phantom" hyperlinks, but here's a stopgap for the "Cannot open the specified file" error:

=IF(ISBLANK(B2),HYPERLINK("#"&CELL("address"),"NO LINK"), HYPERLINK(folderURL & B2,B2))

Instead of displaying just "No Link" if B2 is empty, the formula creates a hyperlink that points back to the cell itself. The cell will still be clickable, but the error message won't show up.

Source: http://www.excelforum.com/excel-programming-vba-macros/340575-solved-conditional-hyperlink.html

Ellesa
  • 11,185
0

Not exactly what the OP is trying to do but this worked for me

=IF(ISBLANK(B2),HYPERLINK("#"&CELL("address"),""), HYPERLINK("http://someurl","friendly_link_name"))

You get an empty, non-clickable cell when there is no link.

aasenjo
  • 1
0

For me worked:

=IF(ISBLANK(B2);HYPERLINK(CONCATENATE("#"; ADDRESS(ROW();COLUMN()));"NO LINK"); HYPERLINK(folderURL & B2;B2))

Using CONCATENATE("#"; ADDRESS(ROW();COLUMN())) in the dummy hyperlink makes the full formula ready to be context aware.

wwk
  • 31