Stop Wasting Time Rewriting Formulas, Save everything in a Scriptionary
When’s the last time you spent over fifteen minutes in Excel fumbling through the formula bar to recreate a formula you already know? The cycle of anger and blame begins as you think “Why didn’t I just save the answer last time?”.
After first cursing at yourself, the programmers of Excel, and finally Bill Gates the answer finally strikes you. In jubilation, you finish up your project, declare yourself a champion of the day and close the file until the next time you need to use this worksheet.
Wait a minute! Remember your frustration from just a few moments earlier? This time, when you figure out that tricky formula, take the extra minute and safely file it away in your Scriptionary.
“What the hell is a Scriptionary”, you might be thinking to yourself. Read the rest of this article to find out and how to use it properly to save you a bunch of time.
Note* I use a Scriptionary for various programming languages and Excel formulas so I might switch between both in my explanation below.
A Scriptionary is a collection of code snippets saved for reuse at a future date. Seasoned programmers might call this a cookbook or something else. Not me, I call it a Scriptionary.
It might be painfully obvious to the readers that golden nuggets of code or a killer formula should be made available for quick access at a later date. There’s a lot of wisdom in our heads we don’t apply as regularly as we should.
If I regularly applied practical advice related to diet and exercise, I would be on the front cover of “Men’s Muscle and Fitness” by now. Ok, maybe not but I wouldn’t be out of breath walking up a couple of flights of stairs.
Here are some tips to optimize your Scriptionary and save you time.
Condense your Scriptionary into a Single Notepad File on your Desktop: I used to have multiple types of script reference files but ran into problems differentiating between them. If you are going to split them up, have one for each different programming language.
Right now I have a Scriptionary for SQL, a separate one for Excel formulas, and another for Powershell. OK maybe I have a few others but this was what I would do if I was a more organized version of myself.
You can have a few different files for storing your scripts. Just don’t turn into a desktop hoarder with a million files all over your workspace. Try to keep it under control.
To Navigate the Scriptionary, organize your scripts by category: Examples of categories include the department you are running the scripts for or the application they are related to.
Add labels in comment format for the Program they relate to:
In SQL they would look like this /*** or this –. This way if you accidentally execute them in a script they won’t error out, or look funky in an editor.
Knowing When to Add vs when to modify Vs when to Leave it out:
I add a snippet of code to the Scriptionary when the script is different enough than my current scripts and tricky to recreate.
If a code is super useful and only one line is different than what you already have in your Scriptionary, add that line to your existing script. Then comment out the new line, maybe even adding an additional comment indicating when to execute this line.
SELECT * FROM CUSTOMERS, CUST_ORDER WHERE
CUST_ORDER.PAYTYPE LIKE ‘Cash’ AND
–To Find only the orders Matching Maximum Quantity
–CUST_ORDER.QUANTITY IN (SELECT MAX QUANTITY FROM CUST_ORDER) AND
AND CUSTOMERS.CUST_ID = CUST_ORDER.CUST_ID
Querying the made-up tables in the example above, notice the 3rd and 4th lines of the query are commented out. The 3rd is a description and the 4th finds where all of the order quantity matches the maximum quantity.
The commented out the line completely changes the purpose of the query, however, it would be redundant to save a brand new query in our Scriptionary as a reference for this one line change. Therefore we comment out the line for now and uncomment it when we need to use it, using the comments as an on-off switch.
Remember the importance of saving snippets of code and important formulas in your Scriptionary so that you can save your future self-time.
With a well-designed set of reference files, you can minimize time spent recreating old solutions and allocate way more working on new stuff. Meanwhile, your nosey coworkers will spend their time trying to peak at your sweet-sweet Scriptionary!
