vlookup in to a hyper link

[VeeraSinha]

Hi,

I have 2 workbooks in the same file. WKBK1 "main" has 2 columns . col 1 is a list of part numbers and col 2 if the "totalcost" of that part number. WKBK2 has 4 columns, first col is same list of parts and col 4 if the total cost and col 2 and 3 are break up of that cost.

I need to make the total cost cell in wkbk1 as a clickable link. so the user can see the cost break up in wkbk 2 . on clicking i need the cursor to go to the matching part row and total cost col in wkbk2.
Heres what I am trying anf the resul is the total cost as a clickable link but when I click on it i get an error. "Can not open the specified file"

=HYPERLINK(VLOOKUP(A18,Details!A:P,16,FALSE))

Please please help . I have a deadline coming up.

Thanks in advance.

Veera

 

[VeeraSinha]

any one out there?

 

[Nogslaw]

Might be kinda messy, but this worked for me:

=HYPERLINK("[book1]Sheet2!A" & MATCH(A1, Sheet2!$A$1:$A$3,0))

You will need to adjust the following to match your data:

Book1: your workbook name
Sheet2!: your sheetname where the details are stored
A (from Sheet2!A): The column you want the hyperlink to take the user to
A1: The cell containing the part number you are looking up
Sheet2!$A$1:$A$3: The range of your part numbers on sheet2
Optional: Add the "friendly_name" before the last parenthesis

 

[VeeraSinha]

I am still getting the error.I changed the formula as below

=HYPERLINK("[Profitability Analysis 6-25-2009 veera]Details!C" & MATCH(A19, Details!$A$1:$A$100,0))

I dont see the finacen cost instead i see the file name as the clickable link.

Am i doing something wrong?

Here are more details:

work book name:
Profitability Analysis 6-25-2009 veera.xls

Worksheet1 name:
Retail Account Analysis
Worksheet 1 format
colA colB
Part number Financecost
ABSCD =VLOOKUP(A17,Details!A:P,16,FALSE)

I want this above finnace cost result to be clickable to go to details worksheet for that row corresponding to ABSCD
worksheet 2 name: Details
Format
COlA col 2 - 14 col16
Part number cost break ups finacencost ( pulled in sheet 1 above)

 

[Nogslaw]

Because you have spaces in your workbook name, you need to enclose it in quotes. Also, to display the result of the formula in the cell, you will need to use the "friendly_name". Try this:

=HYPERLINK("['Profitability Analysis 6-25-2009 veera']Details!C"&MATCH(A19,Details!$A$1:$A$100,0),INDIRECT("Details!C"&MATCH(A19,Details!$A$1:$A$100,0)))

 

[VeeraSinha]

Thanks much for your help.
I can now see the cost values. but the link is still not working . The error is " Cannot open the specified file"

Is it an issue with the hyper link address

 

[Nogslaw]

I actually made a mistake in my previous post. Since the entire reference is in quotes, there is no need to put the workbook name inside of apostrophes. You can remove the two apostrophes (') from the formula.

However, I am now having the same issue as you are. I'm not sure what the problem is. I've tested the formula in a different workbook with simple names like Book1 and Sheet1, and I get the same error.

Not sure what is wrong currently. Let me keep checking for you, and I'll let you know.

 

[Nogslaw]

Okay, I think I found it. Try:

=HYPERLINK("[Profitability Analysis 6-25-2009 veera.xls]Details!C"&MATCH(A19,Details!$A$1:$A$100,0),INDIRECT("Details!C"&MATCH(A19,Details!$A$1:$A$100,0)))

According to another thread on this site, the .xls can sometimes be required even though it doesn't say so in the Help File. https://www.mrexcel.com/board/index.php?threads/380598/

The first couple of times I tested it, it worked without the .xls, but then it stopped working. Once I added this, it seems to work fine.

Note that I also removed the two apostrophes, as they would actually cause the filename to be incorrect.

 

[VeeraSinha]

Yay it worked
VICTORY, HOW SWEET IS THY TASTE!
Thank you so much.

Before you push me off a cliff ,can i ask for an alternative to P in my final formula?

=HYPERLINK("[Profitability Analysis 6-25-2009 veera.xls]Details!p"&MATCH(A16,Details!$A$1:$A$100,0),INDIRECT("Details!p"&MATCH(A16,Details!$A$1:$A$100,0)))

the column will change depending on month number added in the main sheet .so it could be 6 for june or 7 for july.

The detail tab is the exact set of columns replicated for each month .so if Junes finance cost is in col"p". July starts in Col Q and the finance cost for july is in col AE.

I thought of doing a match (as in vlookup) but then how do i get the column letter "p" or "AE" for the hyperlink to work.

 

[Nogslaw]

What is the setup of the detail sheet? Are there any headers? Is there continuous data from A:P?