Hyperlinking to Matched Cell in Seperate Sheet in Workbook

I am attempting to make a formula to create a hyperlink to a customer within a customer list. The customer list on my first worksheet is in column C (cell C7 for this example). It needs to search within column A (from 5 to 2000) on the "Customer List" sheet for that same customer name and link to it.

What I have so far:

=HYPERLINK((INDEX('Customer List'!$A$5:$A$2000,MATCH(C7,'Customer List'!$A$5:$A$2000,0))))

The formula itself is not giving me errors, but when I click on the link it says it "Cannot open the specified file".

I am usually pretty good at figuring these things out (occasionally with help from a google search), but I have been at a dead end for a day or so now and have decided to finally seek help.

Ok, this makes sense.

How can I do this with a semi-simple formula then?

You need the address of the cell, the sheet name and the filename to create a hyperlink to another cell using HYPERLINK.

For example this is a link to A2 in Sheet2 of the workbook Book1.xlsx.

=HYPERLINK("[Book1.xlsx]Sheet2!A2","Goto Sheet2(A2)")

You should be able to concatenate what you need.

That would require knowing the exact cell, right? I'm trying to have it automatically search for the correct cell by customer name. Otherwise, I'd have to update the link all of the time, which is what I want to avoid.

No, not really.

All you need is the row number, I assume you know the rest eg filename, worksheet name, column

You can get the row number using your MATCH formula.

I'd post an example but I don't know your workbook name.

Originally Posted by Norie

No, not really.

All you need is the row number, I assume you know the rest eg filename, worksheet name, column

You can get the row number using your MATCH formula.

I'd post an example but I don't know your workbook name.

Ok...I'm having major issues with my brain working, despite having done a perfectly good MATCH formula before. So, yes, an example would be amazing.



The workbook I have is called "Units".

The sheet I'm linking from is "On Lease" (customer names in column C) and the sheet I'm linking to is "Customer List" with the customer names in Column A of that sheet.

You are amazing! Thank you so much for the help.

Norie,

I am trying to solve the exact same problem, but I am unable to open the attachment you sent to view the solution. Would you be able to post the actual fomulas you recommended so I can see the actual code?

Thanks much.

Tim Green Bay

I figured out my problem and your formula works fine.

Can't thank you enough.