Formula to use vlookup and hyperlink

**paperwings25** · 11-22-2011, 04:27 PM

A buddy of mine asked me to help him and I am currently stuck.

Worksheet 1 is named Outputs. Worksheet 2 is named CustomerSalesData.

- In D7 on the Outputs sheet, the user can input a customer ID number.
- D9 on the Outputs sheet contains the following formula: =IF(D7="","",VLOOKUP(D7, CustomerSalesData!A2:CK6001, 89, FALSE)) This displays the account manager responsible for the account.

- Column A on the CustomerSalesData tab contains all of the Customer ID numbers
- Column CK on the CustomerSalesData tab contains all of the account managers names. This is the 89th column in the sheet which makes the above VLOOKUP formula work.

Here's the problem....

- In D10 on the Outputs tab, I want to put a link that says "Click here to change account manager." This link should take me to the account manager for that particular customer. For example, let's say this is my CustomerSalesData tab:

Column A Column CK
100 Adam
200 Brad
300 Chris
400 Dan

If 100 is entered in D7 on the Outputs sheet, the link in D10 should take me to the cell that says "Adam".
If 200 is entered in D7 on the Outputs sheet, the link in D10 should take me to the cell that says "Brad".

I've tried the following: =HYPERLINK(VLOOKUP(D7,CustomerSalesData!A2:CK6001,89,FALSE),"Click here to change account manager") but I get "Cannot open the specified file".

Anyone know what I'm doing wrong here? Anything I need to be more clear on? Thanks in advance!

**NBVC** · 11-22-2011, 04:42 PM

Try:

=HYPERLINK("#CustomerSalesData!CK"&MATCH(D7,CustomerSalesData!A:A,0),"Click here to change account manager")

**paperwings25** · 11-22-2011, 05:05 PM

I think this worked. I am waiting to hear back to see if it did the job completely. For my own reference, what does putting the # sign do?

**NBVC** · 11-22-2011, 05:23 PM

It is used in order to avoid inserting the path in the formula..

for example you can also use:

=HYPERLINK("[book2.xlsx]CustomerSalesData!CK"&MATCH(D7,CustomerSalesData!A:A,0),"Click here to change account manager")

where book2.xlsx is the name of the workbook you are in...

it just means "in this workbook", in this case...