Home > Data > Data Entry > Entry Form Excel Data Entry Update Alert This version of the Excel data entry form reminds you to save, when you add new records or update existing records.

Introduction

This tutorial shows how the Data Entry form Alert messages and formatting are set up.

For general instructions, go to the Worksheet Data Entry Form page.

Alerts With No Macros

Someone asked how they could remind people to click the Update or Add buttons, while use my Worksheet Data Entry Form.

I thought about adding macros, but that could slow down the workbook, which would be an inconvenience to everyone who uses the data entry form.

So, instead of using macros, I build an alert system based on a few formulas and some conditional formatting. This has only a minor impact on the workbook performance, and will help the people who need reminders.

For example, here's the worksheet data entry alert that appears when the quantity has been changed.

How It Works

Here's how the data engry update alert system works.

First, at the top of the Input sheet, there is a cell named CheckID. In that cell, a formula checks the Order ID, to see if it's already in the database (stored on the PartsData sheet).

=AND(OrderID<>"", COUNTIF(PartsData!C:C, OrderID)>0)

The result will be TRUE or FALSE, and we'll refer to that result in other formulas.

Get the Existing Data

Off to the right on the data entry sheet, there are INDEX/MATCH formulas, to pull the record's current data from the database. Here is the formula in cell G5:

=IF(CheckID=FALSE,"NEW", INDEX(Table1, MATCH(OrderID, Table1[Order ID],0), MATCH(Input!P6, Table1[#Headers],0)))

INDEX/MATCH Formula

That formula starts by checking the CheckID cell. If it's FALSE, the record is not in the database, so the result will be "NEW".

=IF(CheckID=FALSE,"NEW",

Otherwise, the INDEX function returns a value from Table1, which stores the records on the PartsData sheet

INDEX(Table1,

To find the correct row in Table1, a MATCH formula looks for the Order ID in the Order ID column.

MATCH(OrderID, Table1[Order ID],0),

To find the correct column, a MATCH formula looks for the label name in the Table1 headers.

MATCH(Input!P6, Table1[#Headers],0)))

So, for Order ID 10102, the Part is "Spring"

Check for Differences

In column R on the Input sheet, a formula compares the database value (from column Q), to the value in the Input area (in column D)

=IF(CheckID=FALSE,0,--(Q5<>D5))

That formula also starts by checking the CheckID cell.

If it's FALSE, the record is not in the database, so the result will be zero.

=IF(CheckID=FALSE,0,

Otherwise, it compares the values in columns D and Q, to see if they are NOT equal "<>".

--(Q5<>D5)

The result is TRUE or FALSE, and the two minus signs (double unary) change that result to a number (1 or 0):

• TRUE = 1
• FALSE = 0

Colour the Labels

The labels have conditional formatting to change the fill colour:

• Green for new records
• Orange if the value in column D is different from the stored value

The conditional formatting rule for Green (new record) looks at the OrderID and the CheckID cells: =AND(OrderID<>"",CheckID=FALSE)

And there's a simple rule for Orange (updates) – the Difference cell for that row is not equal to Zero:

=R5<>0

Reminder Message

Finally, a message appears below the buttons, with a bright yellow background, if there's a new or changed record.

That's the result of a formula in cell B15:

=IF(OrderID="","", IF(CheckID=FALSE, MsgAdd, IF(AND(CheckID=TRUE, TotalDiff=0),"", MsgUp)))

• No Order ID – empty string
• Not in database – MsgAdd
• In database, no differences – empty string
• Anything else – MsgUp

The MsgAdd and MsgUp named ranges contain the messages, and you can change either message, to whatever text you'd like.

Download the Workbook

Click here to download the Data Entry Form With Delete and Alert workbook.

The zipped sample file is in xlsm format, and contains macros - the alerts don't use macros, but the worksheet buttons do. After you unzip the file and open it, enable macros, so you can use the worksheet buttons.

More Versions: For more versions of the Worksheet Data Entry form, go to these pages:

• Worksheet Data Entry Form page
• Excel Data Entry Form - Basic

More Tutorials

Forms -- Survey Form with Option Buttons

Forms -- Excel Data Entry Worksheet Form

Forms -- Print Selected Items in Order Form

Forms -- Excel Data Entry and Update Form

Basic Excel UserForm

Create an Excel UserForm with Combo Boxes