Get Excel to Automatically Launch Documents from an Exhibit List, Privilege Log, or Hot Documents List

Today I’ll explain how to create a schedule of documents or other files in Excel that includes a hyperlink for each document.  Anyone reviewing this list will be able to open any document described on it by just clicking the link.  This makes it easy to review a list of deposition exhibits or hot documents, or the internal version of a privilege log, and immediately review any document from the list that looks interesting.

Step 1:  Generate a list of the filenames that will be linked in the schedule.  They should all be in the same folder.  You can learn how to do this here.

NB:  If the filenames follow a predictable naming convention such as JX1, JX2, etc. the names can be generated with a formula such as =“JX”&A2 where column A contains the number of each exhibit.

Step 2:  Paste or create the filenames in the column of your choice.  In my example they are in Column A, and I’ve left cell A1 free:

Step 3:  Navigate to the folder that contains the files in Outlook Explorer.  In this example I want to link to the photographs saved in a subfolder called “late 2014” within the My Pictures directory:

Step 4:  Click in the address bar to make the full path come up.  Copy the path by hitting Ctrl+C

Step 5:  Paste the path in a convenient cell in your schedule file.  In this example I’ve pasted it in cell A1.

Step 6:  Use the HYPERLINK formula to generate a hyperlink to each document.  The function has two arguments: 1) the “link location,” i.e., the path to the file, including the filename; and 2) the “friendly name,” i.e., the text of the clickable message that you want to appear.  Excel prompts you for these two arguments when you type out the name of the function and the open parenthesis:

We’ve set the schedule up in such a way that the link location will follow a predictable pattern—we can simply “splice” the path in cell A1 and the file name in the adjacent cell of Column A to get the complete path to the relevant document.  The first formula—to launch the file in cell A3— looks like this:   =HYPERLINK($A$1&A3,”Open File”)

And this is how it appears onscreen:

See how the friendly name is what appears in the cell, while the underlying function you created is visible in the formula bar.  Note the use of dollar signs for the A1 reference (insertable by hitting F4), and the quotation marks around the friendly name text. 

Step 7: Copy the formula down to the rest of the filenames.  You can accomplish this by double-clicking the cross-hairs in the lower right-hand corner of cell B3.  Excel will generate the appropriate formula for each filename, concatenating the path in cell A1 and the correct filename.  Observe how the formula in cell B4 still refers to the path name in A1 (thanks to the dollar signs) but uses the filename in the same row, viz., A4:

Observe how clicking on the link next to the file IMG_1317.JPG launches the picture in a new window:

That’s all there is to it.  The key to making this work is that you have to have access to the folder containing the documents in the same environment in which the Excel file is open.  For example, if your documents reside in a local shared drive, someone in another office will not be able to open the documents from this Excel file because the links will not work.

If you want to make this file portable, make sure you use the correct path for the media where the files will reside.  For example, you could store all your deposition exhibits on a thumb drive, and put this file on the thumb drive as well.  Just make sure that the path you are using to launch the documents is the path that points to the thumb drive, not your computer back at the office.

Thanks for reading.  If you have an idea about how to use this tip in your law practice, please share it in the comments below.  To learn about hosting a CLE-approved Excel workshop at your law firm, company, public interest organization, or bar association, click here or email Ben Kusmin from Excel Esquire.