Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
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.

share|improve this question

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

share|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. – Kaze 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.

share|improve this answer

You must log in to answer this question.

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