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.

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.

dataentryform01

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.

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.

dataentryform02

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.

dataentryform03

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

________________________

You may also like...

2 Responses

  1. Glenn says:

    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

  2. Nikki says:

    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!

Leave a Reply

Your email address will not be published. Required fields are marked *