Check Excel Database Before Adding New Item
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.
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.
If the Order ID is found in the database, the result is TRUE, otherwise the result is FALSE.
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.
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