• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

Compute value for hyperlink in Excel 2007

I know that you can use the Hyperlink function to insert a hyperlink in a cell.  What I need to know is how to make the results of another function that returns the hyperlink work in that function.  
For instance, I can lookup the URL by using the VLOOKUP function As in this example, but I am not getting something quite right (quotes, parenthesis, etc.) to make it work.
=hyperlink((=VLOOKUP([Task '#],BigTaskOrder[[#All],[Task Number]:[Travel Revenue]],58,FALSE)),"Link to Task"))
0
StevenPMoffat
Asked:
StevenPMoffat
  • 2
  • 2
1 Solution
 
Frank WhiteCommented:
Compare with this working solution. Might help. Notice that formulas used as parameters don't take an "=" prefix.

What is "BigTaskOrder[[#All],[Task Number]:[Travel Revenue]]"? This seems to be the primary source of the problem (other than that errant "=" up there), and doesn't correspond to any syntax I usually see in an Excel Formula.

If you're trying to access the range [Task Number]:[Travel Revenue] on sheet #All of workbook BigTaskOrder, you'd use something like: "[BigTaskOrder.xls]#All![Task Number]:[Travel Revenue]"

If this is within the same workbook, well, you're needlessly complicating things. Just [Task Number]:[Travel Revenue] should work, and even better, creating a new named range for this would allow you to put only one range as the target table, which would simplify your entire formula to this:

"=HYPERLINK(VLOOKUP([Task '#],[LookupRange],58,false),"Link to Task")"
0
 
StevenPMoffatAuthor Commented:
=hyperlink((VLOOKUP([Task '#],BigTaskOrder[[#All],[Task Number]:[Travel Revenue]],58,FALSE)),"Link to Task"))

BigTaskOrder is a table in my workbook.   So the range I am trying to access is all rows from column Task Number to Travel Revenue.    

The VLOOKUP formula on its own works and returns the URL. I want it to be the link_location input to hyperlink.   I removed the "=" in front of the VLOOKUP but still doesn't work.  All I see in the cell is the complete string shown above and not the results?
0
 
Frank WhiteCommented:
You seem to have extra parentheses mixing up the function interpreter. Try:

=hyperlink(VLOOKUP([Task '#],BigTaskOrder[[#All],[Task Number]:[Travel Revenue]],58,FALSE),"Link to Task")

If this still doesn't work, the issue is probably specific to that particular method of reference within a hyperlink call or to some obscure variable typing peculiarity with VLOOKUP's return value.
0
 
StevenPMoffatAuthor Commented:
thank You
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now