Hyperlink formula for cell that may or not be a hyperlink

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
149
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))),"")
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    17 KB · Views: 6
  • Capture2.PNG
    Capture2.PNG
    24.7 KB · Views: 6

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,289
Office Version
  1. 2016
Platform
  1. Windows
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 "#".
 
Solution

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
149
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!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,630
Messages
5,597,277
Members
414,133
Latest member
lucid33

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top