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.


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

| improve this answer | |
  • This doesn't appear to be a bug. Per Office documentation: "When you click the cell that contains the HYPERLINK function, Microsoft Excel opens the file that is stored at link_location." – gm2 Mar 21 '13 at 20:07
  • @gm2 I think you're misunderstanding the OP's question. Excel is creating a "phantom" hyperlink even if you don't mean to. – Ellesa Mar 26 '13 at 3:04
  • I understand the behavior of Excel that the OP describes, and I think your answer is a nice solution (and I +1'd it) without using VBA. My point was that the current Office documentation suggests that this behavior is intended. I understood it to mean "Excel will act on a cell containing HYPERLINK() by following a link that's presented as an argument". Taking another look now, however, the docs could be more explicit and maybe mention this limitation. – gm2 Mar 26 '13 at 5:32
  • Interestingly this "Unusual Behavior" was confirmed to be a problem in Excel 97. – gm2 Mar 26 '13 at 5:40

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.