When you build an Excel tool or template, it’s rare that you’re ever really finished building. There’s always something that would make the tool a little better, either for your own use, or for your customers.
And that’s the case with the Excel worksheet data entry form, which I’ve just updated again.
The original version was by Dave Peterson, and the form has evolved into a version in which you can add and update items in the database.
New Features
In the latest version, I fixed an issue with the navigation. Thanks to Travis, who let me know about the problem.
Now, when you move to a different record with the arrow buttons, the Order ID selector also updates.
You can see the Order ID, the Order ID selector, and the record number, circled in the screen shot below.
Add or Update
The other enhancement is a database check, when you click the Add or Update button. In a hidden column, a COUNTIF formula counts the selected Order ID occurrences in the database.
=AND(OrderID<>””,COUNTIF(PartsData!C:C,OrderID)>0)
If the Order ID is found in the database, the result is TRUE, otherwise the result is FALSE.
Warning Message
If you try to add an Order ID that already exists in the database, you’ll see a warning message, shown below.
- Click Yes, to update the record
- Or click No, enter a unique ID number, and click Add again.
Message – Not in Database
Or, if you try to update a record and it’s Order ID isn’t in the database, you’ll see a different warning.
- Click Yes to add the record, instead of updating.
- Or, click No, enter an existing ID number, and click Update again.
Download the Updated Form
To download the new version of the worksheet data entry form, please visit the the Data Entry and Update Form page on the Contextures website.
The download link is near the bottom of the page.
And if you have any ideas for future improvements, please let me know!
More Articles on the Worksheet Data Entry Forms
Website pages With Sample Files
Data Entry Worksheet Form – Basic
Worksheet Data Entry Form – Enhanced
Blog Posts
Worksheet Data Entry Form in Excel
Edit Records in Excel Worksheet Data Entry Form
New Improved Excel Data Entry Form
How to Customize the Excel Data Entry Form
Delete Orders with Excel Data Entry Form
________________________
Hi, I’ve been trying to utilize your program to create and entry and update form for my sales figures. However, my spreadsheet doesn’t run row by row entry. It must find the sales person first, then find the date for the entry they want to either update or put in a new entry (its the days of the current month). I can seem to get it to work correctly – the update or new feature. Id like to use the Index/Match feature to locate the inputs – if they have one (even if its empty), then use the Update function to let them make edits to their number inputed for that particular date. Any help would be appreciative. Can this form you have here do that type of changes?
Thank you – Glenn
We have a large database of our clients email addresses on an excel spreadsheet. Can your program give us a prompt when we are trying to add an email that is already in the database? We want to be prompted when an email is duplicate and which email. Can your program do that? Please help!