Dynamic Hyperlinks in Excel to HockeyDB

About 16 years ago I discovered HOCKEYDB. I was hooked! Why? Growing up in the late 70s and 80s I was a huge hockey fan but without cable or the internet information was limited. I had so many unanswered questions.



Hockeydb is an archive for hockey statistics, trades, logos, draft picks, hockey cards checklists etc. It also has a daily update of team standings and transactions called The Morning Report.


So Many Reasons To Visit HOCKEYDB.COM

I was able to start answering questions like:

  • How many career points did Henri Richard have?
  • What players were involved in the Phil Esposito trade?
  • What year was Bunny Larocque the leafs #1 goalie


My First NHL Game

It was unforgettable. February 17 1979. It was my dad’s 47th birthday (my current age). At this exact moment 39 years ago today we were driving to the game.

Nothing can compare to walking in and seeing the ice surface of Maple Leaf gardens. I remember Mike Palmateer bouncing all over the ice and seeing my favorite player Darryl Sittler. Wow! The leafs played the L.A. Kings. I followed Marcel Dionne whenever he was on the ice!

But the star that night was definitely Walt McKechnie.

Walt scored a hat trick as the leafs won 5 to 2Using hockeydb I can explore Walt’s career and other players from both teams.




Discovering the WHA

Just a few months after seeing my first NHL game I heard that the WHA was ending and four of their teams would join the NHL. I didn’t even know that the WHA had existed! Why didn’t anyone tell me?

The 79-80 NHL season was incredible as a teenager from the WHA named Gretzky took the NHL by storm. NHL legends Dave Keon, Gordie Howe and Bobby Hull who had played in the WHA all played their final NHL season with the Hartford Whalers. Hockeydb makes it easy to explore this interesting year and stats from the WHA.


Exploring The NHL Backwards

My first memory of Phil Esposito was as a Ranger playing against the Habs in the NHL finals. By reading his o-pee-chee hockey card I discovered that he had played with Bobby Orr on the Bruins. Hockey cards are great but HockeyDB is a treasure chest of free knowledge that has enabled me to go back and explore thousands of details.

For example, I only remember the very end of Stan Mikita’s career but hockeydb makes it easy to explore his career, teammates, etc. Search for a player and then click a link to see his teammates for any year! You can also click ‘Show Trades’ and ‘List Cards’.


Who Is Behind HOCKEYDB.COM ?

Ralph Slate launched hockeydb in 1996 and has kept growing it. He is the principal data architect at ISO New England. He loves data. He loves hockey. Very cool. Read more about Ralph on masslive.com and globeandmail.com .

From the GlobeandMail:


Slate has spent about 20 hours a week maintaining HockeyDB over the years. During its two decades of operation, he’s compiled a set of encyclopedias’ worth of digital information: some 186,531 players, 7,220 teams, 416 leagues, 2,131 logos and 6,071 sets of hockey cards.


What Does This Have To Do With Excel?

The best way to navigate hockeydb.com ? Go there and explore! Nevertheless, I thought that it would be fun to build dynamic hyperlinks to various places within hockeyDB.com using Excel’s hyperlink functionHere is my Excel file.


How Do Dynamic Hyperlinks Work?

Static Hyperlink

A static link would go to the same website every time. Example:



Dynamic Hyperlink

Notice this part above “0000381975“. “38” is the team code (Maple Leafs). “1975” is the year.

If we want to see the L.A. Kings roster in 1979 we need team code “40” and year “1979“.

I use drop down lists to allow you to select the team and the year.


Then I concatenate everything together inside the HYPERLINK function.

In cell F22 I used this formula to put all the pieces together:

=HYPERLINK(F$13&F$14&F$15&F$16&F$17&F$18,F$3&” “&F$17)

F$13&F$14&F$15&F$16&F$17&F$18″ concatenates everything and F$3&” “&F$17 is the display text.


About Me

My name is Kevin Lehrbass. I work as a Data Analyst in Toronto.

This is my personal blog about Microsoft Excel.

Long before I worked with Databases and Microsoft Excel I loved the data found in hockey & baseball cards. I also devoured the amazing information in Zander Hollander’s sport’s year books.

Leave a Reply

Your email address will not be published. Required fields are marked *