# Solved: HYPERLINK and VLOOKUP



## aalegrete (Jul 27, 2008)

Hi

I hope you can help with this.

I have created a table on sheet 1. In one of the cells of that table, I have attached a hyperlink pointing to another cell in that same table. In sheet 2, I use the HYPELINK(VLOOKUP) formula to copy the contects of the cell on sheet 1 containing the hyperlink. The contents are pulled over to sheet 2 and it displays just like a link. However, when you click on the link I get an error instead of being taken to the correct cell on sheet 1. I have a sample file called Test Doc.xls which I uplaoded.

The formula I'm using to pull the contents of the cell on sheet 1 is as follows:
=HYPERLINK(VLOOKUP((Sheet1!$C$5),Sheet1!$D$9:$F$10,2,0))

Any help on this would be much appreciated.

Thanks,
Anthony


----------



## slurpee55 (Oct 20, 2004)

Your link calls Launch Process 072308.xls. When I open it on my machine, it looks for a file called "BP" (getting that from cell E10 on Sheet1). 
=HYPERLINK("[C:\Documents and Settings\aalegret\My Documents\Test Doc.xls]Sheet1!$C$5",(VLOOKUP((Sheet1!$C$5),Sheet1!$D$9:$F$10,2,0))) 
will take you to Sheet1 - but note that you have to define the location to go to completely (e.g., if you have it on your desktop and not in my documents, that must change - and the cell to go to must be defined. I don't think you can look up a cell and derive the hyperlink from what is there, if that is what you are trying to do.


----------



## aalegrete (Jul 27, 2008)

Slurpee

I used the full url you gave me but changed the cell to D9. This takes me to the correct cell when the initials are AA, but when I select the next name from the drop down list in sheet1 C5, the intials change to BP on sheet2 like I want but the link is still pointed to sheet1 D9 instead of changing to D10. I want the url in D4-D7 sheet2 to follow the name selected in sheet1 C5.

Your final assesment seems to be what I want to do. But I don't understand whyit can't be done. You can cut and paste a cell and it's contents including a URL to any other cell and a url will work properly. Is there any way to do what I want with some formula?

Thanks for the help,
Anthony


----------



## slurpee55 (Oct 20, 2004)

But you aren't copying and pasting a URL - Excel is able to make allowances for the changes in location when you do that. What you are trying to do is have Excel create the URL based on information derived from a LOOKUP.
Perhaps an IF formula would work, if there are just a few possible locations - e.g. =IF(VLOOKUP((Sheet1!$C$5)="AA",HYPERLINK("[C:\Documents and Settings\aalegret\My Documents\Test Doc.xls]Sheet1!$D$9",HYPERLINK("[C:\Documents and Settings\aalegret\My Documents\Test Doc.xls]Sheet1!$D$10")


----------



## Aj_old (Sep 24, 2007)

slurpee55 said:


> But you aren't copying and pasting a URL - Excel is able to make allowances for the changes in location when you do that. What you are trying to do is have Excel create the URL based on information derived from a LOOKUP.
> Perhaps an IF formula would work, if there are just a few possible locations - e.g. =IF(VLOOKUP((Sheet1!$C$5)="AA",HYPERLINK("[C:\Documents and Settings\aalegret\My Documents\Test Doc.xls]Sheet1!$D$9",HYPERLINK("[C:\Documents and Settings\aalegret\My Documents\Test Doc.xls]Sheet1!$D$10")


slurpee wouldn't be easier to use a cell in which is stored the path to the workbook, and use in formula reference to this cell?
let say

```
A1 = C:\Documents and Settings\aalegret\My Documents\Test Doc.xls
```
and formula:

```
=IF(VLOOKUP((Sheet1!$C$5)="AA",HYPERLINK("[" & $A$1 & "]Sheet1!$D$9",HYPERLINK("[" & $A$1 & "]Sheet1!$D$10")
```
and for A1 to use an UDF to get the Workbook's path and name?


----------



## Aj_old (Sep 24, 2007)

The formula in A1 could be: 

```
="[" & SUBSTITUTE(LEFT(CELL("filename", A1),FIND("]",CELL("filename", A1))), "[", "")
```
, without using any UDF, or if OP want can be used an UDF

Antony, take a look at the file I uploaded, is this that you wanna be done?


----------



## slurpee55 (Oct 20, 2004)

Thanks for coming in AJ - I suspected that we needed someone with more knowledge here, but was struggling through..your solution is both clearer and probably faster - would be interesting to test it....


----------



## aalegrete (Jul 27, 2008)

AJ
Yes! This works exactly how I want it to. 

The only way I figured out after I posted my cry for help was to use 2 cells on sheet1. 1 to get the initials and the other to define the url location of the workbook including the name of the workbook.

Sheet 1 second cell > #Sheet1!D9
Sheet2 D4 > =HYPERLINK(VLOOKUP(Sheet1!$C$5,Sheet1!$D$9:$G$10,3,0),(VLOOKUP(Sheet1!$C$5,Sheet1!$D$9:$G$10,2,0)))

With this I was able to get what I needed and also be able to move the file from PC to PC and have the links stay operational. This is a more awkward way to handle what I want because I have to add a second cell of information, but your solution doesn't require any extra data input.

I don't have any pretention to understand the whole formula that you sent me, but I'd like to. The Standard Excel help isn't very helpfull so where else can I look?

By the way, in order to get as speedy a response as I could, I also sent my cry out to another forum as well and got back the following that I thought you might be interested in.

I just place this in D4 of sheet2.

=HYPERLINK("#"&CELL("address",INDIRECT("'Sheet1'!D"&MATCH(VLOOKUP(Sheet1!$C$5,Sheet1!$D$9:$F$10,2,0),Sheet1!E:E,0))),VLOOKUP(Sheet1!$C$5,Sheet1!$D$9:$F$10,2,0))

Thanks allot for the help and solution. I really appreciate it,
Anthony


----------



## slurpee55 (Oct 20, 2004)

aalegrete, glad AJ figured this out for you - please use the button at the top of the page to mark this thread as Solved.


----------



## Aj_old (Sep 24, 2007)

Hi Antony


aalegrete said:


> This is a more awkward way to handle what I want because I have to add a second cell of information, but your solution doesn't require any extra data input.


My solution requires an additional input too! If you'll take a look at the formula you'll the reference to the cell $A$1, it's not visible because I used white color font, for it to not be visible, but it's there. In this cell is the full path to the workbook. Take a look at it!

If you don't wanna use an extra cell for input, use the formula you get from another forum, or it's modification:

```
=HYPERLINK("#"&CELL("address",INDIRECT("'Sheet1'!D"&MATCH(Sheet1!$C$5,Sheet1!$D$1:$D$10,0))),VLOOKUP(Sheet1!$C$5,Sheet1!$D$9:$F$10,2,0))
```
The decision is yours!


----------



## aalegrete (Jul 27, 2008)

Yes, I saw the A1 reference when I first looked at your solution. What I meant by not having to add extra info was each time I added a new name, I didn't have to fill in a secondary cell with information. Your solution only required the 1 change at the begining while setting up the worksheet.

Thanks again for all the help. I will look at the modified formula you sent.

Anthony


----------



## Aj_old (Sep 24, 2007)

Glad to help!


----------

