If you litigate in the New York courts, then you’re familiar with SCROLL–the court-maintained web site where you can enter a case’s index number and pull up the docket for that case. Now wouldn’t it be nice if you could hit one button and go straight to the SCROLL docket for a given case, instead of going to the website and typing in the index number? Today I’ll show you how to do just that, using Excel’s HYPERLINK function. If you have to keep track of a lot of cases this could be a real timesaver.
The SCROLL site allows you to search for cases by index number, party name, or other fields (in the advanced search).
Regardless of how the search is done, clicking on the case you want will take you to a page with a predictable URL. See the examples below:
Notice how the URL of any given docket consists of the following string of text (in the red box above) followed by the Index Number of the case:
By pasting this string–let’s call it the “generic docket URL”–and the desired Index Number into Excel’s HYPERLINK function, we can get Excel to take us straight to the SCROLL docket, without actually going to the SCROLL home page and typing in the Index Number. And we can generate these links automatically for a long list of index numbers. Here’s how.
First, an explanation of Excel’s HYPERLINK function. It consists of two arguments:
1) the URL or web location you want the link to go to; and
2) the message that should appear to represent the link
For example, I could create a link to the New York Times homepage that says “Check the news” with the formula shown below:
Clicking the link generated by the formula will launch the New York Times homepage in a new browser window.
Now suppose I represent McDonald’s and I want to track a number of different cases wending their way through the New York courts. I’ve created a chart that shows the Matter Name, Client Matter #, and Index Number for each case.
I paste the generic docket URL in an out-of-the-way place, in cell A1.
In the column called Docket List, we’ll create a formula that combines the generic docket URL with the Index Number (using the & symbol) to derive the docket location on SCROLL, and the message “Go to docket”:
=HYPERLINK($A$1&C4,”Go to docket”)
Here’s how the formula works:
- The string $A$1&C4 combines the generic docket URL (in A1) and the Index Number (in C4) to produce the correct web address of this Index Number on SCROLL :
- The $ signs in the A1 reference make it an absolute reference, which means that it won’t change when we copy the formula. When writing the formula, you can simply click on cell A1 and then hit F4 to insert the $ signs. The C4 reference does not have the dollar signs (a so-called “relative reference”) because we want that reference to change when we copy the formula down.
- The phrase “Go to docket” (you need the quotation marks in the formula) tells Excel what message to display on top of the hyperlink.
Now you can copy this formula down to all of the rows by grabbing the cross-hairs on the lower-right-hand corner of cell D4 (the cell with the first formula) and dragging those cross-hairs down to as many rows as you want to copy the formula. Each new formula will create a link for the Index Number in that row.
Observe how the formula in D17 correctly uses the text in A1 and the Index Number in C17 to create the proper hyperlink.
From now on you can come straight to this chart when you want to get an update on all the active cases. The first time you click a link in a given browsing session you will probably have to respond to SCROLL’s ‘captcha’ test to prove that you are a human being.
After you enter it once, for the rest of the same browser session the links should take you straight to the docket.
I hope you enjoyed this post. If you have any other ideas for creating dynamic hyperlinks, please post them below or email me directly. Follow the blog to learn about new posts, and about CLE-accredited Excel workshops.
Oh, sweet! I was able to do this for an Excel sheet full of references from a PubMed search. (Not legal, I know…. but something I was really looking to do). The records have a predictable link beginning, “http://www.ncbi.nlm.nih.gov/sites/entrez/”, then a unique PMID number that I already had in one column of the spreadsheet. I created a separate link column with the URL and made the “friendly name” the author of each article, dragged it down and voila! 600 citations all with a nice clean hyperlink. YAY, thanks!
Pretty! This was an incredibly wonderful post. Thank you for supplying this information.