I have a spreadsheet with a list of part numbers. I would like to access the drawing for each of these part numbers from a specific directory when the link is selected. The part number has two parts, Name_Revision.pdf. Revision can be alphanumeric (ZZ to 00).

I would like to be able to pull the latest Revision from the folder, so the function needs to cycle through the Revision code from the highest to the lowest (ZZ to 00), concatenate that with the Name_ and open the PDF file returning only the latest valid revision.

I have successfully opened a file with the following Hyperlink:
=HYPERLINK("\\Path\" & A18 & "_" & "a" & ".pdf", A18), where A18 contains the Name and the "a" is the revision (this is where the function would be inserted to pull the latest revision).

Is there an easy way to implement this code. Any help would be greatful.