MS Excel - using the Hyperlink formula

Thomas Zucker-ScharffSenior Data Analyst
CERTIFIED EXPERT
Veteran in computer systems, malware removal and ransomware topics.  I have been working in the field since 1985.
Published:
Updated:
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!
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:
  1. Type of file
  • Survey – for survey responses
  • CV – for curriculum vitae returned
  • Biosketches – for biosketches returned
  1. Last name
  2. First initial of first name
  3. Month the file was received - M/MM
  4. Day the file was received - D/DD
  5. Year the file was received – YY
  6. File type ending of .pdf
  7. 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:
  1. Firstname
  2. Lastname
  3. Email sent
  4. Email received (by recipient)
  5. Recipient responded
  6. Response with Survey
  7. Response with Biosketch
  8. Response with CV
  9. Day responded (numeric – 1-2 digit)
  10. Month responded (numeric – 1-2 digit)
  11. Year responded (numeric – 2 digit always 17)
  12. 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.
  1. Named ranges / cells
    1. 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
    2. I created named ranges for
      1. The unchanging part of the file path (path_start)
      2. The survey part of the file path and the beginning of the name (surveys)
      3. The biosketch part of the file path and the beginning of the name (bios)
      4. The CV part of the file path and the beginning of the name (cvs)
  2. The If function
    1. Probably one of the most used functions in excel
    2. Syntax is =if(logical test1, then true value, else false value)
  3. The concatenate function
    1. Lets you “weld” together a strings of text strings
    2. Syntax is =concatenate(text field1, text field 2, etc)
  4. The left function
    1. This lets you decide which characters you wish to display
    2. Syntax is =left(text field, number of characters from start)
  5. The hyperlink function
    1. A beauty of a function this lets you turn text into a hyperlink and give it a human readable name
    2. Syntax is =hyperlink(hyperlink text, human readable name)
  6. 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:
excel-article-table-1-pict.PNG 
I use the following formula in the summary tab to generate a unique link to the file(s):
=IF('raw data'!F2=1,HYPERLINK(CONCATENATE(path_start,surveys,Table4[@LNAME],",",LEFT(Table4[@FNAME],1),," ",Table4[@[resp month]]," ",Table4[@[resp day]]," ",Table4[@[resp year]],pdf),"Survey"),"")

Open in new window


This does the following:
  • 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
  • select hyperlink
  • 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.
 
3
2,242 Views
Thomas Zucker-ScharffSenior Data Analyst
CERTIFIED EXPERT
Veteran in computer systems, malware removal and ransomware topics.  I have been working in the field since 1985.

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.