Hyperlink help

**PaulJM** · 01-14-2012, 05:36 AM

Hi

I have a pretty basic knowledge on excel so please bear with me. I have created a spreadsheet and in the final column have put in a hyperlink to link to a folder on a network drive.

The folder contains about 200 other folders (customer names) with about 20,000 files (part numbers) in total. I want the hyperlink to go to a specific file in one of the folders depending on what part number is typed in another column on the spreadsheet ie column B is the part numbers so when the hyperlink is clicked on that line it searches the folder for for that particular part number. Is this possible?

If you want me to post the spreadsheet I can

Thanks

Paul

**arlu1201** · 01-14-2012, 06:10 AM

You can do this via VBA. Just post the sample spreadsheet and provide the folder path.

**PaulJM** · 01-14-2012, 06:50 AM

Hi Arlette

Thanks for the reply, I have attached the sample. The folder path would be V:drillshop\drill history

I would like the link in column H to find the part number written in column D

Thanks again for any help

Paul

**Haseeb A** · 01-15-2012, 01:11 AM

Hello Paul,

Use HYPERLINK function. H2 copy down.

=HYPERLINK("V:\drillshop\drill history\"&D2&".xls","Click for file")

If it is not works replace V: with server name, \\Server name\

**PaulJM** · 01-15-2012, 04:17 AM

Hi Haseeb

Thanks for the help, I tried that formula but the link still just opens up the main folder with all the other folders inside. It doesn't search for the part number in column D unless I'm doing something wrong.

Thanks

Paul

**PaulJM** · 01-15-2012, 04:33 AM

So if you click the link in column h and part number 222222 is in column D I want it to open up drill history (main folder) / customer 2 (sub folder) / 222222 (part number)

Regards

Paul

**Haseeb A** · 01-15-2012, 03:16 PM

My last post was assuming, part number is inside the drill history without any sub folder. If you have sub folders in drill history & you are NOT sure in which sub folders contains the part numbers file. In that case VBA only the way to loop on those folders & open it.

If all the part numbers are in a specific folder you can add an additional path in HYPERLINK. eg:

=HYPERLINK("V:\drillshop\drill history\Customer\"&D2&".xls","Click for file")

Or, if your customer folder in drill history is same (NOT case sensitive) as entered in Col_C, you can use like,

=HYPERLINK("V:\drillshop\drill history\"&C2&"\"&D2&".xls","Click for file")

**PaulJM** · 01-16-2012, 10:29 AM

Hi Haseeb

I don't know which sub folders contain the part numbers so I guess it will need VBA.

Would it be possible for you to help me with the code for this please?

Thanks

Paul