Do you use a spreadsheet like Microsoft's Excel? Have you ever wanted to link out to a non excel file on your computer or network drive? This is the way I found to do it!
Using the Hyperlink formula in Excel by Thomas Zucker-Scharff under a Public Domain Work license
I am in no way an expert when it comes to Microsoft Excel. I use it often (daily), but consider myself just a user. I sometimes stumble upon, what I believe to be, items of interest when working with excel, this was one of those items.
It started a couple of years ago when I was searching for a way to create a hyperlink in excel from several fields. I didn’t just want a hyperlink to a website, which seems to be what is used most often, I needed to link to a file on a network drive that was not another excel file (another seemingly frequent need). When I initially did this I found an excel function online. The function I had found extracted the already created URL, which was the opposite of what I wanted - I wanted to create a link.
What I had
This is a great example of what the built in formulas can do. The scenario was as follows:
I was tracking the returns of documents to our center in a spreadsheet and I wanted to be able to link directly to the files that were returned from the spreadsheet if and only if there was a file that existed. The returned files were being saved in a prescribed way. The name would always contain the following elements in this order:
Type of file
Survey – for survey responses
CV – for curriculum vitae returned
Biosketches – for biosketches returned
First initial of first name
Month the file was received - M/MM
Day the file was received - D/DD
Year the file was received – YY
File type ending of .pdf
The file would always be saved in certain directories in the same base file path (\\network drive\share\subdirectories\ )
The final result might look something like this: \\mynetworkshare\shared directory\subdirs\Annual Survey of members\Survey_zucker-scharff,t 2 1 17.pdf
I have a spreadsheet with several worksheets (tabs). The fields in the first worksheet (called "raw data") are:
Email received (by recipient)
Response with Survey
Response with Biosketch
Response with CV
Day responded (numeric – 1-2 digit)
Month responded (numeric – 1-2 digit)
Year responded (numeric – 2 digit always 17)
Extended for other information
The second worksheet contained a summary of the data from the “raw” tab with names in three columns and the corresponding Survey, Biosketch and CV fields
In order to create a link to the correct file on the network share, I used several features of excel with which you may or may not be familiar.
Named ranges / cells
This lets you name a range or cell with a recognizable name (such as survey) instead of referring to it as az1, or more aptly, $az$1
I created named ranges for
The unchanging part of the file path (path_start)
The survey part of the file path and the beginning of the name (surveys)
The biosketch part of the file path and the beginning of the name (bios)
The CV part of the file path and the beginning of the name (cvs)
The If function
Probably one of the most used functions in excel
Syntax is =if(logical test1, then true value, else false value)
The concatenate function
Lets you “weld” together a strings of text strings
Syntax is =concatenate(text field1, text field 2, etc)
The left function
This lets you decide which characters you wish to display
Syntax is =left(text field, number of characters from start)
The hyperlink function
A beauty of a function this lets you turn text into a hyperlink and give it a human readable name
Syntax is =hyperlink(hyperlink text, human readable name)
Last, but not least, I have frozen the summary and report tabs so that you can’t scroll away from that information. (thanks to Rob Henson – robhenson, a member of experts exchange for showing me this one)
So if the first line in the spreadsheet (raw tab) looks like this:
I use the following formula in the summary tab to generate a unique link to the file(s):
IF('raw data'!F2=1 - If the statement is true and there is a number 1 in the field (indicating that the item was responded to and a corresponding file was saved) then
HYPERLINK( - create a hyperlink to the file
CONCATENATE( - by putting together the following text
path_start – the fixed part of the path to the file
surveys – the part of the file path that indicates that it is in the survey directory and the beginning of the name of the file that indicates it is a survey (“\Surveys\Survey_”).
Table4[@LNAME] – the last name of the researcher
“,” – a comma
LEFT(Table4[@FNAME],1) – the first letter of the first name of the researcher
Table4[@[resp month]] – the month that the person’s response was received
" " - space
Table4[@[resp day]] – the day the response was received
“ “ - space
Table4[@[resp year]] – the year the response was received
Pdf – the named range/cell representing the ending “.pdf”
Friendly name – the name you use when seeing the text
Else if there is not a “1” in the cell (the statement if false) display nothing
Now all this can be done fairly easily with VBA scripting, but why bother when Excel gives you this ability without having to learn any real coding at all.
So what can you do now?
I have linked to the spreadsheet below. You are of course welcome to use any part or all of it to suit your own needs. By entering simple data into the raw data tab, the rest of the tabs are automatically populated. For your own needs I HIGHLY recommend first creating a link by doing the following:
Right click on a field
browse to find your document
Click okay or press enter
Then hover your mouse over the link to see how it is formatted. This is how I noticed that Excel was formatting my links to network files as "file:///\\<network drive>\<share>\<folders>\<file>".
If you are interested in my other article on MS Excel and scripting it with outlook for mailmerge, you can find it here. You can find all my articles on my profile page.
If you find this article useful, please don't forget to vote it helpful and leave feedback below.