I'm looking for a user friendly solution for my problem in excel 2010, cause multiple people will work with the same workbook.

I get a list with up to 1000 entries of data every other weak. This list contains a receipt ID and I have a scan of that receipt with the same name (C:\scans\1234567891011121314.pdf) on a shared drive.

a b c d
1 Merchant Receipt ID
(~20 digits number)
Amount 10 more colums of data
2 Apple 1234567891011121314 100 x
3 Banana 1234561413121110987 90 y

I want to create a Hyperlink in the chart wich is named after the receipt ID that pops up the appropriate PDF File. The problem is that the folder with the scaned PDF got ~10.000 files already and I would like to speed up the process of finding the right PDF for the data in the sheet...and I need to use a filter on the whole thing as well.

At the moment I crop the ID from the Path in about 5 steps but hopefully there is a easier way to get to it without copying it back and forth as value after using LEFT/RIGHT/PART etc.
Afterwards I search for the scan in my folder and the row in the sheet and combine both by hand in a HYPERLINK formula.


If there is another way to get it done I would realy appreciate it!