Entries in hyperlinked exhibit list (1)

Thursday
Jun272013

Exhibit Lists - Do You Hyperlink Them?

Sometimes our clients ask us if we can hyperlink their exhibit list or index.  We are always more than happy to assist.  However, when working on a recent project, I learned of a way to hyperlink an exhibit list in Excel that blew my mind.  I felt compelled to share it even though it may result in some clients no longer requiring our assistance for this particular service.

Excel is an awesome program. I love going through the Microsoft template site and seeing spreadsheets that users developed to make certain tasks easier. I even took an online class to learn more about how to utilize the program. I have always thought of creating links in Excel as quite an arduous task, until now. With a simple setup and file organization you can “automatically” link your exhibit list.

To begin, this is a quick guide and works with linking to PDF files. You can link to other file types, but I want to keep this simple.

This guide also assumes that all of the files are going to be in the same folder. You can modify the formulas to direct the links point to subfolders, but again I am aiming for simplicity.

Let’s get into the nitty gritty.

First, filenames are important for this to work correctly. For this example we will keep it general using an EX####.pdf schema. You may use a DX, PX or JX prefix depending on which side you represent.

Next, move all of the exhibits into a folder. For this example I have them in a folder named SmithvJones on my C:\ drive.

Then copy the spreadsheet into the same folder as the exhibits.  Rename it by putting underscore in front of the file name so it will appear first in the folder. Your folder should look like this.

 

Open the spreadsheet and insert a new row and a new column.  Row 1 and column A will now be blank.

Also notice the “Exhibit Number” column. The easiest way for this to work is to have it match the filenames of the PDFs you have in the folder.

Select cell A1 and insert in the following formula:

=CONCATENATE(LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1))

When you enter that formula in you should notice that the cell now populates with the path of where your exhibit list is located.

 

Next, you are going to copy the exhibit numbers from the “Exhibit Number” column and paste them into column A.

Now select the cell with the first exhibit number. In this example it is cell B3. Once you have that cell type in the following: =HYPERLINK(CONCATENATE($A$1,A3,".pdf"),A3)

You may not notice anything change with the text, but if you hover over the cell you will see a dialogue box appear describing a hyperlink. This formula is creating a hyperlink based on the file path created in cell A1 then merging it with the text in column A and adding a .pdf at the end. The final part of the formula uses the text from column A to populate the cell with user-friendly text.

 

You can now copy that formula for the rest of column B.

Finally, you can adjust row 1 and column A over so they aren’t visible and your end product looks like this:

 

You could go even one step further and make the text blue to designate that you hyperlinked your exhibit list. If you choose to add this step, this is the shade of blue I like to use.

 

 

I hope you find these formulas as useful as I did. I look forward to hearing your experience with it!