Use a Spreadsheet to Create Lists of Hyperlinks

The humble spreadsheet is one of your best friends on the other side of the screen. It can be used to manage your business, keep track of animation keyframes, do the math to get to Jupiter, and maybe a few other things. It’s a single-environment coding workhorse disguised as a desktop office application.

Creating and maintaining an archive of files which are hyperlinked can be a tedious task. A text editor breaks down pretty quickly and it doesn’t hold a candle to using a spreadsheet.

When I use a spreadsheet to create hyperlinks from a list of files, I use the name of the target file as a base to generate both the link and description. HTML and CSS can be added if need be. Here is sample Excel spreadsheet - the examples in the file are basic but this method can be expanded to handle much more complex (and useful) tasks. The spreadsheet includes a preview of the links, a screen capture of the result, and explanations of functions “&”, Left(), Find(), Mid(), and Substitute() which are used to create the links from the file names. Open it up, change a few values in the colored source cells or the strings in the formulas, and you’ll see how it works in no time.

One of the great conveniences of this approach is making global changes such as renaming files, changing the link text or fixing mistakes. For example, say I have:
Handout_1.zip
Handout_2.zip
Handout_3.zip
Handout_4.zip
Handout_4b.zip
…

and I setup the sheet to generate this hyperlink:

<a href=”http://aplaceontheweb.com/adir/Handout_2.zip "target="_blank"> Handout 2&nbsp; — &nbsp;142 MB</a></br>

In a browser the links would display as:
Handout 1 - 242 MB
Handout 2 - 67MB
…

Along comes the boss who reviews the page and asks who those handouts are for. Oops, they are for the search and rescue team! I can manipulate the spreadsheet to change the description, leaving the links unchanged (there are a number of simple ways to do this). Maybe I would end up with this:

Search & Rescue - Handout 1 ….. 242 MB
Search & Rescue - Handout 2 ….. 67 MB
…
Changing CSS assignments and/or HTML container type is a also a snap.

A More Complex Example
In our organization we publish documents in English and Spanish, with some containing both languages. I track the status of all the docs in a spreadsheet,. Adding an additional sheet to create hyperlinks leverages the power of the spreadsheet and makes the process more efficient. All of the data in the link creator sheet is linked to the list of documents in the tracking sheet so changes are propagated throughout the catalogue.

Caveat
All of this is predicated on organized file naming conventions. If you’re not practicing basic data management you should put that task near the top of your to do list… I’ve heard it said that Rome fell because they did not discover the zero.

A shoutout to ExcelJet.net. They have an excellent collection of Excel examples and a reference for looking up Excel functions.