Part look up that will allow hyperlinking to the correct file/folder

[Blopez787]

I am trying to have info pull up from one sheet "Parts w/material" to another sheet "Part Look-up" by typing in the part number and wanted the hyperlinking follow in the Part Look-up from the "Parts w/Material" sheet. So when someone types in the "ENTER PART#" field the material hyperlink will link you to the material spreadsheet, same for customer, the link will direct you to the customer folder, and the part # will be link to the part folder.

I attached spreadsheet so you can see what i'm trying to do.

Thanks in advance.

Part number - Material - Customer 5-8-12.xlsx

[Max, Singapore]

In Part Look-up,
For Material, try this in B4:
=IF(B3="","",HYPERLINK("#"&CELL("address",INDIRECT("'Parts w-Material'!E"&MATCH(B3,'Parts w-Material'!B:B,0))),INDEX('Parts w-Material'!E:E,MATCH(B3,'Parts w-Material'!B:B,0))))

[Blopez787]

Thanks for the help Max,

But it just sends me back to the list of parts, I really don't want it to go back to that sheet.
I have all my material being recieved in under seperate execl file, what i am looking for is on the Parts w-Material I am going to Hyperlink link all them to the correct material execl file, the correct customer folder, and the correct part folder. Then hide that page "Parts w-Material" so no one can mess with it/or get confused because there alot of different material sizes we use. So the "Part Look-up" sheet is were I would them just to enter in the part number there looking for and it will pull up what material it is, customer, and part folder. When they click on the material it will take them to the material execl file, or the customer folder on the server. Is this possible?

[Max, Singapore]

Ah, I see. Hyperlinks to hyperlinked cells and the whole thing to work/flow straight through. Not sure whether its possible, but I don't know any further, sorry. Perhaps you might wish to try posting in excel.programming forum.

[Blopez787]

I'm going to try to use your formula and just password lock the workbook. THKs.

Now is there a way to highlight the cell when it goes back to the Parts w-Material sheet?
This way they know which one it is.

[Max, Singapore]

Well, you could try posting in programming forum for active cell highlighter code, but I'm not sure whether this code might disrupt the core hyperlink functionality that you have on that sheet

Are you happy with my earlier responses? If so, do take a moment to click the little star at the bottom left of those responses