Hyperlink formula to cell with data

Brownick

New Member
Joined
Sep 16, 2014
Messages
10
Hello all,

I am currently trying to use a hyperlink formula, along with index & match, to be able to click a cell and follow the same hyperlink from a cell in a back tab. The front tab is called Template, back tab is titled Photo Tracking.

My current equation is =HYPERLINK(INDEX('Photo Tracking'!C:C,MATCH("*"&'Template'B13&"*",'PHOTO Tracking'!K:K,0)),"OK")))

When I hyperlink a cell on Photo Tracking and leave the name of the file, this equation works fine; however, when I input a value (1-4) over the hyperlinked cell the formula stops working and I get the message "Cannot open the specified file"

The hyperlink still works on the Picture Tracking tab, but does not on the front tab.

I'm not really sure what the issue is, so any help you can provide would be greatly appreciated.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi Brownick,

Try this syntax....
=HYPERLINK("#'Photo Tracking'!C" & MATCH("*"&Template!B13&"*",'Photo Tracking'!K:K,0),"OK")
 
Upvote 0
Good morning Jerry,

When I use your formula and click "OK" it takes me the Photo Tracking tab, while what I'm looking for is for it to open the hyperlink that is in the cell on Photo Tracking if that makes sense.

Also, instead of Template the front tab is titled Draw Request. I don't think if the formula still works since it's 2 words.

Any advice is appreciated.
 
Upvote 0
When I use your formula and click "OK" it takes me the Photo Tracking tab, while what I'm looking for is for it to open the hyperlink that is in the cell on Photo Tracking if that makes sense.

I'm not understanding what you mean by "open the hyperlink that is in the cell on Photo Tracking". Could you provide a more detailed explanation with example addresses and hyperlink target addresses?
 
Upvote 0
Sorry, I wasn't exactly sure the best way to explain it but I will try my best.

In my workbook on the front tab 'Draw Request' people will be entering a 4 digit number in cells B13-B38. When they do I would like the corresponding column Q cell to return either OK or NO if the 4 digit number can be found in column K on my back tab titled 'Photo Tracking'.

If it returns OK I would like to be able to click OK and have it open the same hyperlink that is in column C of the same row as the 4 digit number on photo tracking. (I.E. I enter the number 0740 in 'Draw Request' B13, column Q returns OK because on 'Photo Tracking' the number 0740 is found in K5 and when I click the OK is opens the same hyperlink that is in 'Photo Tracking' C5.)

Currently I am using this equation
=IF(ISBLANK(B13),"",IF(ISNA(VLOOKUP("*"B13&"*",'Photo Tracking'!K5:K93,1,FALSE)),"NO",HYPERLINK(INDEX('Photo Tracking'!C:C,MATCH("*"&'Draw Request'!B13&"*",'Photo Tracking'!K:K,0)),"OK")))

Now, when I go to the 'Photo Tracking tab' and right-click cell C5 to hyperlink to a document titled "1 Loretta.xlsx" the equation works perfectly. I can type 0740 into cell B13 on 'Draw Request', Q13 will return OK and when I click OK, 1 Loretta will open up.

However, in the 'Photo Tracking' tab I don't want to display 1 Loretta.xlsx in cell C5, I would like to display the number 4. When I type 4 and click cell C5 the hyperlink still works, but when I return to the 'Draw Request' tab and click the OK in cell Q13, I get the error message "Cannot open the specified file".

It seems by having a different text to display, excel is having trouble following the path and I'm not sure why.

Thank you for reading, and any help would be greatly appreciated.
 
Upvote 0
Thanks for the detailed clarification. :)

The problem is that the formula in 'Draw Request'!Q3, isn't actually following the hyperlink that is in 'Photo Tracking'!C5. The reason that works when the value in 'Photo Tracking'!C5 is "1 Loretta.xlsx", is because the formula reads that Text String then uses it in the HYPERLINK formula in 'Draw Request'!Q3. You can verify that by putting the text: "1 Loretta.xlsx" in 'Photo Tracking'!C5 with no hyperlink C5 and the Hyperlink in in 'Draw Request'!Q3 will still work when you click OK.

There isn't any way for Excel's standard formula functions to directly read another cell's Hyperlink's Target Address.
Your workarounds would include:

1. Using a helper column (let's say Column D, that could be hidden) to store the Hyperlink's Target Address. The values in Column D would be read by the HYPERLINK formula in 'Draw Request'!Q3. Column C would be used for the Display Text you want the user to see.

2. Using a VBA User Defined Function (UDF) to read another cell's Hyperlink's Target Address. The UDF could be used within the formulas in 'Draw Request' column Q.
 
Upvote 0
Jerry,

Thanks for the clarification, I had a feeling it wasn't possible.

Do you have any advice on how I can do that with a UDF? I'm not very familiar with VBA.
 
Upvote 0
UDFs can be used in worksheet formulas very much like built-in Excel functions.
Here are some instructions on how to setup your workbook to use the UDF below.

1. Starting with your Excel workbook open, save a copy of your workbook in a file format that allows macros:
File > SaveAs then save the file to an .xlsm format (for Excel 2007 or later versions)

2. After your workbook is saved hit Alt+F11, which will open the VBE (Visual Basic Editor).

3. From the top menubar of the VBE: click Insert > Module

4. Paste the code below in the new window that opens on the right

Code:
Public Function GetHyperlinkAddress(rCellWithHyperlink As Range) As Variant
'--returns the target address of the first hyperlink object
'    associated with referenced range
'--if no hyperlink is found, returns #N/A

 Dim vReturn As Variant
 
 If rCellWithHyperlink.Hyperlinks.Count = 0 Then
   '--return #N/A if no hyperlink
   vReturn = CVErr(xlErrNA)
   GoTo ExitProc
 End If

 With rCellWithHyperlink.Hyperlinks(1)
   If Len(.SubAddress) Then
      '--# is needed to reference ranges in this workbook
      vReturn = "#" & .SubAddress
   Else
      '--for hyperlinks to external files or URLs.
      vReturn = .Address
   End If
 End With

ExitProc:
 GetHyperlinkAddress = vReturn
End Function

5. Exit back to Excel by keying Alt+Q.

That's it! The UDF "GetHyperlinkAddress" is ready to use as a custom workbook function.

Test that out with a very simple formula entered into a cell in sheet Photo Tracking:
=GetHyperlinkAddress(C5)

If cell 'Photo Tracking'!C5 has a hyperlink, then the target address of the hyperlink should be displayed in the cell with the UDF formula (eg: "C:\TEST\1 Loretta.xlsx")
If no hyperlink is found in C5, the cell with the UDF will display #N/A.

If that test works as intended, you can use the UDF in more complex formulas.

Here's one formula that could be used to achieve the objective with the layout you've described....
In 'Draw Request'!C13 enter:

Code:
=IF(ISNUMBER(MATCH("*"&'Draw Request'!B13&"*",'Photo Tracking'!K:K,0)), 
    HYPERLINK(GetHyperlinkAddress(INDEX('Photo Tracking'!C:C,
      MATCH("*"&'Draw Request'!B13&"*",'Photo Tracking'!K:K,0))),"OK"),"NO")

That formula is similar in approach to the one you've been using.

If you want shorten your formulas, some of the logic that's in the formula above could be moved to the UDF.
For your first UDF, I wanted to keep the VBA code relatively simple. :)
 
Upvote 0
Jerry,

Thank you so much, what you have provided works perfectly.

My last question is if there is a way to make the formula read from bottom-top, rather than top-bottom? I would like it to find the last iteration of the 4 digit code in column K of 'Photo Tracking' rather than the first.

Thank you for all your help.
 
Upvote 0
My last question is if there is a way to make the formula read from bottom-top, rather than top-bottom? I would like it to find the last iteration of the 4 digit code in column K of 'Photo Tracking' rather than the first.

That could be done through formulas or VBA. The challenge is to do that in a way that has minimum impact on performance.

Array formulas with entire column references like 'Photo Tracking'!K:K can significantly slow the calculation of your Workbook.

Because of that, my first thought was to use another UDF to return an index to a Cell in Column C corresponding to the last match in Column Q. When I tried that, the cells with matching Hyperlinks flickered rapidly when the cursor hovered over.

I learned from that and a little web search, that when you have a formula as one of the input parameters to the Hyperlink worksheet function, Excel tries to evaluate that parameter repeatedly when the cursor hovers over the Hyperlink. I believe this is so Excel can display a tooltip with the Hyperlink address. That wasn't much of a problem for the previous relatively simple UDF, but for a more complex UDF, the user will observe some distracting flickering as Excel does that evaluation.

Based on that, I'd suggest you use an array formula to get an index to the cell in the last match's row, but limit the range of the lookup range.

To make the formulas simpler and easier to maintain, you could use a few Named Ranges as follows:

Name: rngLookupVector
Scope: Workbook
Refers to: ='Photo Tracking'!$K$1:$K$200

Name: rngResultsVector
Scope: Workbook
Refers to: ='Photo Tracking'!$C$1:$C$200

This is a Row-Relative Named Range, so before defining it, select the Cell: 'Draw Request'!A1
Name: ptrLookupLast
Scope: Workbook
Refers to: =INDEX(rngResultsVector,MAX(-1,IF(ISNUMBER(SEARCH("*"&'Draw Request'!$B1&"*",rngLookupVector)),ROW(rngLookupVector)-ROW(INDEX(rngLookupVector,1))+1)))

With those Names defined you should be able to enter this formula into 'Draw Request'!B13, then copy down...
Code:
=IF(LEN(B13)=0,"",IF(ISERROR(ptrLookupLast),"NO",
    HYPERLINK(GetHyperlinkAddress(ptrLookupLast),"OK")))


Based on your OP, it looks like sizing the lookup ranges at 200 rows would work for your current setup.
The named ranges make that easy to change if your lookup range grows.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,151
Messages
6,170,382
Members
452,322
Latest member
CrimsonCoure

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
Back
Top