Using Spreadsheets for Data Entry Forms
We’re half-way through our "Top 10 Dysfunctions of #Spreadsheets" — # 6 in the list? Using spreadsheets for #DataEntryForms. What to do? 👇
You may use spreadsheets for data entry, finding this to be a natural and easy way to start collecting information. You may already have a shared Google Sheet with all of your teammates … why not just as a few questions and have people fill it in? 🤔
Ok, perhaps for a certain use case that you have confidence will not change or spread or sprawl … maybe that works.
For example, you may have a list of parts for an urgent order. You ask the team to enter the quantity on hand for each SKU to do a quick stock take to see if we can get it out the door today. ‼️
In our experience, it typically doesn’t stop there … your needs may evolve, the questions may change from a flat response into requiring a rich response. Now you need to know the suppliers or assemblies for each part, and that creates a #oneToMany… and those don’t fit too well in a 2-D spreadsheet. 📦
Most good forms also reference live and accurate pick lists. Your teammate may fat-finger a SKU# into your survey, and now you have some confusion that needs to be clarified, that is if you catch it before the notes go off to the sales team! ⛔️
Instead, a good form will give the users a #pickList of SKUs, including displaying the part description to confirm that they picked the correct part. In general, you want your teammates to pick rather than free-form type!
What should you do about it?
1. If you are stuck with #spreadsheets for now, consider at least creating a few example responses in the first few rows, and an explainer key off to the side. You may also want to define the data types you expect to the entire column (ie. date, text, number).
Adding symbols such as $ and “,” and defining the decimals expected can help guide the user toward more accurate data entry. That little comma will help ensure that you actually have 1,000 parts of SKU ABC, rather than only 100!
Consider also adding “chips”, pickers, drop-downs or controls to the columns. That will make your spreadsheet more form-like.
2. In a #RapidApp development environment such as #Claris #FileMaker, you can create forms rapidly, referencing live data to create fast, type-ahead pick lists, and control / validate the results.
You may also be able to create a simple #queue to see who has yet to enter their information, #notify users of their #progress through the queue … or better yet, dashboard the results in real time!
Heck, you may not even need that stock take if you build it out right: you might simply need to run a report and then get that urgent order confirmed for sales in real-time!
Thank you.
