Hyperlink with Vlookup to New worksheet in same workbook "Cannot open Specified File"

[epohlabel]

Hello!

I have a workbook "Consolodate.xlsx". It has a worksheet of accoutn information, a worksheet of Contacts, a worksheet of associated Email information and an Overview worksheet. I want to be able to hyperlink from the Overview worksheet to the Account worksheet based on a lookup to the correct account.

The formula I am using is

=HYPERLINK(VLOOKUP(B2,Account!A:B,2,FALSE),"Account Link")

Where B@ is a the account ID on the Overview sheet, Account!A:B is the Array with the Account ID in column A. I would think this would feed the cell in column B on the Account sheet where the associated AccountID is found in Column A into the location portion of the hyperlink formula. I get no syntax errors when creating hte formula - but when I click the link I get the "Cannot open specified file" error.

I have checked that the Hyperlink Base field in the file properties is blank.

 

[stumac]

Try this:

=HYPERLINK("#Account!B"&MATCH(B2,Account!A:A,0),"Account Link")

 

[epohlabel]

That worked!!!! THANK YOU SO MUCH!!!

 

[epohlabel]

I have a 2nd part of this. The "Email" worksheet actually might contain multiple rows that match to a single row in the Account worksheet. I would like to include multiple columns in the Account worksheet "Email 1", Email 2" etc. Can I modify the hyperlink code above such that I can specify to Match only the 1st, 2nd, 3rd etc row found?

 

[jbmccarthy]

Try this:

=HYPERLINK("#Account!B"&MATCH(B2,Account!A:A,0),"Account Link")

I have a similar issue, but a little more involved. I have one worksheet that I use as a lookup (HFRD!$B$2:$J$114) to return the result from column 9, which is a link to another worksheet in the workbook. When I use the below code, I get the link with the same result as above " Cannot open the specified file." I am not having much luck with the MATCH command. Any ideas would be greatly appreciated. Thanks so much!

=IFERROR(HYPERLINK(VLOOKUP(C2,HFRD!$B$2:$J$114,9,FALSE)),"")

 

[stumac]

What is being returned? Just the cell name or the sheet and the cell?

It should be the format: '#SheetName!A1'

The # at the beginning may need to be added to the formula.

=IFERROR(HYPERLINK([COLOR=#FF0000]"#"[/COLOR]&VLOOKUP(C2,HFRD!$B$2:$J$114,9,FALSE)),"")

 

[jbmccarthy]

Thanks so much for your quick reply. The cell I am attempting to lookup is just a static cell with a link to another sheet in the workbook. I am hoping to just import or reference that same cell content, which would then link to the desired sheet. The lookup sheet (HFRD) has numerous entries, and I used to just do a find on that sheet to look for the desired information, but I can only do one at a time. I created this second sheet to be able to paste in a list of desired lookups, and to pull the related info from the lookup sheet. Everything works well, except for the cell with the link. I was not familiar with the "#" you are referring to, but I now understand it to be a shortcut for the workbook name; however, I am getting "Reference isn't valid." I also played with the single ' because my sheet names have spaces, as well as changing the spaces to _, with negative results. Thanks again for your input.

 

[IggyMBSP115599]

Try this:

=HYPERLINK("#Account!B"&MATCH(B2,Account!A:A,0),"Account Link")

I have a similar issue, I see the Hyperlink cell but it will not work.
=HYPERLINK(VLOOKUP(P2&"|"&F5&"|"&G5&"|"&H5,HYPER!E2:F7,2,FALSE))