Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hyperlink in a Formula? (2002 SP3)

    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.
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Hyperlink in a Formula? (2002 SP3)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>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,'Pr oject Listing'!$A$2:$C$159,1<font color=red>)</font color=red>,""<font color=blue>)))</font color=blue>

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Hyperlink in a Formula? (2002 SP3)

    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,'Pr oject 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

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink in a Formula? (2002 SP3)

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

    Does this do what you want?

    <pre>=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,'Pr oject Listing'!A2:C159,1),"")))
    </pre>

    Formula corrected. Thanks Steve.
    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink in a Formula? (2002 SP3)

    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.
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink in a Formula? (2002 SP3)

    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.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink in a Formula? (2002 SP3)

    Hans, works like a charm! <img src=/S/bullseye.gif border=0 alt=bullseye width=45 height=15>
    And I see I(I think) that the hyperlink needed the file name also. Can you explain the last part of the formula?
    <hr>=IF(ISERROR(MATCH(B3,'Project Listing'!$A$2:$A$160,0)),"", HYPERLINK("[Loungelookup.xls]'Project Listing'!" & ADDRESS(MATCH(B3,'Project Listing'!<font color=blue>$A$2:$A$160,0) <font color=red>+1</font color=red>,3 ),"See Projects Tab"))</font color=blue> <hr>
    What does the +1 do? Is it related to the & ADDRESS? I'm confused about the <font color=448800>&ADDRESS(MATCH( </font color=448800>

    Thanks a lot!!
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink in a Formula? (2002 SP3)

    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
    <code>
    "[Workbook.xls]'Worksheet'!D37"
    </code>
    In your situation, the first part is fixed:
    <code>
    "[Loungelookup.xls]'Project Listing'!"
    </code>
    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.

    <code>MATCH(B3,'Project Listing'!$A$2:$A$160,0)</code> 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? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink in a Formula? (2002 SP3)

    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.
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink in a Formula? (2002 SP3)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •