vlookup in to a hyper link

VeeraSinha

New Member
Joined
Jul 6, 2009
Messages
8
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
 
Last edited:

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Nogslaw

Well-known Member
Joined
Jul 9, 2004
Messages
904
Might be kinda messy, but this worked for me:

Code:
=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

New Member
Joined
Jul 6, 2009
Messages
8
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

Well-known Member
Joined
Jul 9, 2004
Messages
904

ADVERTISEMENT

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:

Code:
=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

New Member
Joined
Jul 6, 2009
Messages
8
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

Well-known Member
Joined
Jul 9, 2004
Messages
904

ADVERTISEMENT

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

Well-known Member
Joined
Jul 9, 2004
Messages
904
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.
 
Last edited:

VeeraSinha

New Member
Joined
Jul 6, 2009
Messages
8
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

Well-known Member
Joined
Jul 9, 2004
Messages
904
What is the setup of the detail sheet? Are there any headers? Is there continuous data from A:P?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,780
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top