Microsoft Excel Question?

Here is my dilemma. Keep in mind I am not that computer savvy. The company I work for utilizes Microsoft Excel to keep track of all their CAD drawings. When a drawing is complete it goes into a shared area (just a shared folder) under its part number. So if a part is just released an example of the part number would be 123456.1.pdf. They are saved as .pdf’s hence the .pdf, the .1 means revision 1, and the 123456 is the part number. The excel document then contains this part number and the description and a hyperlink to this .pdf document. Here’s where the problem comes. When drawings get updated so for this example we would have 123456.2.pdf, they get placed into the same shared folder, the old revision gets moved and the hyperlink gets changed. The problem I have is that I do a ton of these and having to change the hyperlink from a .1.pdf to a .2.pdf becomes cumbersome. Is there any way in excel that I can use a macro or something else where the hyperlink would read the latest revision. So if both .1.pdf and .2.pdf were in the same directory it would pull up the .2.pdf when you clicked on the hyperlink? Thanks for your help.