Stop Wasting Time Rewriting Formulas


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!

keepersecurity.com

Latest Blog Posts

Learn about SQL joins using Excel

Learn about SQL joins using Excel

Why do we join tables in SQL? Tables that logically represent information are the foundation of relational databases. The ancient rules of database normalization remind us to arrange database tables to reduce redundancy and increase flexibility. An adequately...

Super Bowl Squares Template

elink.io | See Original The Superbowl is almost here. If you are tasked with creating blocks for a pool, there's still time to pull out your ruler, draw up a grid, and wrangle a bunch of friends into writing their names in the squares. Or, spare yourself some time...

How to Return Multiple Matches in one Cell Using a lookup?

VLOOKUP is great for looking up a value and returning another corresponding value. But what happens when the first match just isn't enough. I've been tasked with finding a way to put return not just the first but the second, and third match. At first, I tried to...

How to Delete every other Row in Excel

How to Delete every other Row in Excel

Have you ever needed to delete every other row in Excel? You are not alone.   There are a few ways to accomplish it. It's a pretty good candidate for a VBA solution, but I'm going to show you how to quickly delete every other row without using VBA or a plugin. I...

Data Defender: Play this Excel Game and Learn Excel Shortcuts Fast

Data Defender: Play this Excel Game and Learn Excel Shortcuts Fast

Microsoft Excel has roughly 750 million users all over the world. It is an essential, all-purpose business tool. This spreadsheet software can fill a wide range of business needs. Every business professional needs to get familiar with Excel. Your proficiency level...

Select the Email Service Provider that is right for you.

Select the Email Service Provider that is right for you.

  Excelshortcut.com commits to continuous improvement. I've wondered if our Communication distribution process is lacking. Is there a more effective tool to help us share valuable content? Mailchimp has been ExcelShortcut.com's tool for email creation and...

Planning Software
Share This