Hyperlink in a Formula? (2002 SP3)

Topic

New Reply

I am trying to create a formula that will Vlookup on the CC column to another tab and if the Lookup is True it will put a Hyperlink to the other tab. I would like to have the text displayed for the Hyperlink be some generic text (i.e.

Reply | Quote

Replies

This will add the Text you want to the cell (based on a modification of Legare’s formula):

=IF(ISERROR(VLOOKUP(B4,’Project Listing’!$A$2:$C$159,3)),””,IF(VLOOKUP(B4,’Project Listing’!$A$2:$C$159,3)>0,HYPERLINK(VLOOKUP(B4,’Project Listing’!$A$2:$C$159,1),”See Projects Tab”)))

I am not sure what you are after, since you aren’t looking up a valid “hyperlink Address” in the datatable…

Steve

Reply | Quote

Thanks Legare and Steve. The formula works except when you click on the hyperlink there is an error message “cannot open the specified file.”

Steve, in answer to your question, what I really want is, if a CC has a project (s) I want to put a link to the Projects listing tab. The formula puts the right text in the hyperlink and correctly identifies CCs that have projects but clicking on the link doesn’t work.

Reply | Quote

Try the attached.
WARNING: don’t open the file directly, but download it and rename it to Loungelookup.xls. The formula I used

=IF(ISERROR(MATCH(B3,’Project Listing’!$A$2:$A$160,0)),””,HYPERLINK(“[Loungelookup.xls]’Project Listing’!” & ADDRESS(MATCH(B3,’Project Listing’!$A$2:$A$160,0)+1,3),”See Projects Tab”))

uses the file name.

Reply | Quote

Hans, works like a charm!
And I see I(I think) that the hyperlink needed the file name also. Can you explain the last part of the formula?
[indent]

---

=IF(ISERROR(MATCH(B3,’Project Listing’!$A$2:$A$160,0)),””, HYPERLINK(“[Loungelookup.xls]’Project Listing’!” & ADDRESS(MATCH(B3,’Project Listing’!$A$2:$A$160,0) +1,3 ),”See Projects Tab”))

---

[/indent] What does the +1 do? Is it related to the & ADDRESS? I’m confused about the &ADDRESS(MATCH(

Thanks a lot!!

Reply | Quote

The first argument to the HYPERLINK function is a string value specifying the hyperlink address. If this is a location in an Excel workbook, the string must be of the form

"[Workbook.xls]'Worksheet'!D37"

In your situation, the first part is fixed:

"[Loungelookup.xls]'Project Listing'!"

This is concatenated with the address of the cell you want to jump to. To get this address, we use the ADDRESS(row, column) function. The column number is 3, for column C, and the row number is obtained from the MATCH function.

MATCH(B3,'Project Listing'!$A$2:$A$160,0) looks up the value of B3 in A2:A160 on the Project Listing sheet; the 3rd argument 0 specifies that we’re looking for an exact match. The function returns an index number: 1 if a match was found in the 1st cell, 2 if a match was found in the 2nd cell etc. Since we’re looking in the range A2:A160, we must add 1 to this result to get the actual row number.

Clear as mud now?

Reply | Quote

Hans,
Actually, I think I understand it. I worked through each part of the formula and it makes sense. Well, almost. The only thing that still confuses me is the +1. I took it out just to prove it to myself and it Hyperlinks to the cell above – so I see that you need it. But I’m not sure why.

Reply | Quote

The MATCH function looks up the search value in the range A2:A160.
If MATCH returns 1, it means that the search value was found in the very first cell in the range, i.e. in A2.
If MATCH returns 2, it means that the search value was found in the second cell in the range, i.e. in A3.
In general, the row number of the cell where the search value is found is one more than the result of MATCH; this is the +1. This is because the range A2:A160 starts in the second row of the worksheet, not in the first row. If you had searched in A1:A159, you wouldn’t have had to use the +1. On the other hand, if you had searched in A3:A161, you’d have had to use +2, etc.

Reply | Quote

Line break inserted by HansV to prevent horizontal scrolling. The formula should be entered as one line, though.

Does this do what you want?

=IF(ISERROR(VLOOKUP(B3,'Project Listing'!$A$2:$C$159,3)),"",IF(VLOOKUP(B3,
'Project Listing'!$A$2:$C$159,3)>0,HYPERLINK(VLOOKUP(B3,'Project Listing'!A2:C159,1),"")))

Formula corrected. Thanks Steve.

Reply | Quote

You have a parenthesis in the wrong place. I think you meant:

=IF(ISERROR(VLOOKUP(B4,’Project Listing’!$A$2:$C$159,3)),””,IF(VLOOKUP(B4,’Project Listing’!$A$2:$C$159,3)>0,HYPERLINK(VLOOKUP(B4,’Project Listing’!$A$2:$C$159,1),””)))

Steve

Reply | Quote