Hyperlink formula for cell that may or not be a hyperlink

[melodramatic]

I'm creating a query for a client data, wherein by putting in the client name, the query pulls down all of the info from the hidden data sheet.

One problem I'm having is for our Restrictions field. In some cases, the restrictions are brief, and typed out. However, in others, the client restrictions are saved on the server as a pdf file, and the link to that file is in the data sheet.

What I've done (or rather, tried to do) is created a formula that tells the cell (on the query page, not the data page) that if the data field starts with "\\", put in a hyperlink to the location, with the friendly showing of "CLICK HERE FOR REQUIREMENTS." Easy enough.

However, for the portion of the formula if the data field does NOT start with "\\", it tries to hyperlink to nothing. I looked around here first, and saw that once you put the hyperlink work in your formula, it wants to make a hyperlink, regardless of any if statement. The gent who helped the person last year with the problem suggested going ahead and making the second part of that if statement a hyperlink that pointed to the cell being used.

With that brief explanation of what I'm trying to do done, here's what I've got.

Cell D25 - I put in a formula to do exactly as I said. Playing with a lot of Indirects within that formula, but the basic idea is that it will send me to the file, or send me to the cell just above if it's not a true link.

When the client I've chosen has a hyperlink file, everything works perfect. My "CLICK HERE..." shows, and they hyperlink brings up the networked pdf file perfectly.

However, when the client I've chosen has only notes, then if I click on that cell, it takes me to "Cannot open the specified file."

I'm obviously not putting in the cell link (as opposed to file link) correctly.

Help?

Formula: =IFERROR(IF(LEFT(INDIRECT("'"&$C$7&"'!S"&$A17),2)="\\",HYPERLINK(CONCATENATE(INDIRECT("'"&$C$7&"'!S"&$A17)),"CLICK HERE FOR REQUIREMENTS"),HYPERLINK(D24,INDIRECT("'"&$C$7&"'!S"&$A17))),"")

 

[Toadstool]

Hi Melodramatic,

It's difficult to put a test together without seeing all referenced cells. Can you provide a more complete example?

P.S. Don't forget if you're linking within the same workbook you'll need to prefix the address with "#".

 

[melodramatic]

And you gave me the key I needed to solve it. The "#" before the cell reference - that fixed my problem!

=IFERROR(IF(LEFT(INDIRECT("'"&$C$7&"'!S"&$A17),2)="\\",HYPERLINK(CONCATENATE(INDIRECT("'"&$C$7&"'!S"&$A17)),"CLICK HERE FOR REQUIREMENTS"),HYPERLINK("#D24",INDIRECT("'"&$C$7&"'!S"&$A17))),"")

Thank you!!!!