i currently have an excel sheet where i am entering the issued certificates for the jobs done.
Some examples for the certificate names are
FEB18/190/C1 & FEB18/190/MPI/C1
I have similar names for the files that i save. But the problem is that as windows does not accept "/" in the file name i have replaced "/" with "." in the file names and they look like
FEB18.190.C1 & FEB18.190.MPI.C1.pdf
It would be very difficult for me to link them manually, as there are alot of jobs. And there are not just certificates that i have to deal with. i have invoice, quotation, delivery notes with the same requirement.
Now i want to be able to click the cell with certificate name text and i want the file to open.
And i want the hyperlinks to update automatically as i put the certificate name. if there is no file with that name it should stay as plain text.
It would be easier if the color for the certificate name text can change when it gets hyperlinked so that it becomes easier to identify that it has been linked.