Auto Hyperlinking to specific files in folder by name

I haven't been able to find anything on here so I apologize if this may have already been asked. I have an excel sheet where is cell A1 is the number 2000 and in cell A2 is the number 2001 and so on.....I then have a folder with a PDF named 2000......what formula do I enter so i can have it automatically hyperlink when I add a specific file to that folder by number? I don't know if what i am saying sounds correct but need some help!!

In cell B1, you can enter the following formula...change the folder name to the directory of your files:

=HYPERLINK(CONCATENATE("C:\FolderName\",A1,".pdf"),A1)

Thank you kkal117!! It worked!! Now here is one more question!! I see it automatically changed the color to blue like something was linked but some of them don't have a PDF. How can I change that in my spreadsheet so they don't change color UNLESS there is an actual file linked.

Not sure how to code your request. However, the following code will retrieve the pdf file names in a specific directory into column A and add a hyperlink to the file. You'll have to replace the FolderName with the directory you want.

Please Login or Register  to view this content.