asked on

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"))

Frank White

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")"

StevenPMoffat

ASKER

=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?

ASKER CERTIFIED SOLUTION

Frank White

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

StevenPMoffat

ASKER

thank You