Data Validation (DVAL) is a seldom used feature first found
in Microsoft Excel 2000 and later versions that CPA’s and financial types may
find especially useful.
DVAL allows spreadsheets to restrict the type of data that
is entered into certain cells. And in a more robust sense, DVAL allows
spreadsheet designers to guide users on how to enter appropriate information
into worksheets or forms.
Here are some common examples:
-
In a purchase order, only integers between 1 and 99 are
allowed in the quantity column.
-
In an expense report, only dates are allowed in the
dates field.
-
When entering budgets for the new year, the amount may
not exceed 150 percent of last year’s amount.
-
For invoices, the model number field must be
chosen from a drop-down list of products currently for sale (an example will
be shown later)
As DVAL is set up, designers may provide a message to all
users working with a particular field, and other messages to users who attempt
to enter unallowable data.
The best way to learn, however, is with an example. Let’s
pick up where we left off before: we developed an invoice using the VLOOKUP
function (Editor’s note: See
VLOOKUP Function of Excel, The Business Edge, March 2006). Here,
if users entered the correct model number and quantity, then the spreadsheet
looks up the correct price. Now we can go two more steps and demand that (1) the
quantity sold must be a minimum of 10 and a maximum of 100, and (2) the Model
Number is selected from a list of available products, and cannot be manually
entered.
Example
Let’s say your company sells items that appear in the July price.table.
We will work with the Invoice60201 tab in the file
tsbom.com/d/BusinessEdgeDataValidationArticle.xls. Save this file
with an .xls extension to a local disk before continuing or certain
features described in this article may not work. Then, open and see that
rows 13 through 17 are for entry of invoicing items; rows 13 through 16
are already set up with Data Validation rules; and row 17 is for you to
complete. If you are unsure, look at the previous rows to see how it’s
done.To set up the quantity rules, select cell D17, do Data >
Validation > Whole Number, and then enter 10 as the minimum and 100 as
the maximum. Now choose the Error Alert tab, and enter an alert message
that Excel displays when the entry rule is violated. In this case,
invoice preparers would not be allowed to continue, and the following
alert message appears: “The quantities allowed for wholesale customers
are a minimum of 10 units, and a maximum of 100. Integers only. See
Ralph for exceptions.” If you are having trouble, refer to Cells D13
through D16 which are already completed. Then test by entering various
values into cells D13 to D17 to see what happens.
Note that we can specify a Style of warning in this same Error Alert
Area. The drop-down choices are Stop, Warning or Information. Stop means
that the user simply cannot enter disallowed data. Warning and
Information are almost the same, except that Warning displays the alert
message and asks users if they want to continue, whereas Information
displays the alert and assumes users want to keep right on going.
The “Available Models Number Only Rule” is a bit more complex due to
a shortcoming of Data Validation. The problem is that we want Excel to
allow only model numbers that are on the july.pricetable tab, but DVAL
cannot deal with cells that are not part of the current tab. We get
around this by entering references to the july.pricetable into an
unprintable part of each invoice tab, as seen in cells A39:A42 with
formulas such as =july.pricetable!$A$7.
Now let’s get going on the rule by selecting cell A17, and clicking
Data > Validation. Then choose the Settings tab, and in the Allow
drop-down box select List. Enter the range A39:A42 where the allowable
model numbers reside. Notice in (already completed) cells A13:A16 an
Error Message and similar Input Message directs that “You must enter a
model number from the drop down list.”
This characteristic is valuable: invoice preparers cannot sell
out-of-stock items or provide attractive wholesale prices to retail
customers.
But don’t rely on Data Validation for security. It’s good for making
things easy but clever users will find ways around the data entry rules,
for example, by copying and pasting nefarious entries into any cell.
Other unstoppable DVAL breaches involve using Edit > Fill, or to sell
more than 100 units; then just enter the same model number on 2 lines
(e.g., sell 62 then 64 of model TS118300 as was done in rows 14 and 15
on Invoice60201). Protecting and hiding certain cells helps some, but
we’ll deal with that in another article.
Beyond this, a convenient way of auditing for allowable entries is to
do View > Toolbars > Formula Auditing. A floating toolbar appears. Click
the fourth icon from the right, where a “Circle Invalid Data” message
appears on mouse-over. All invalid data is then circled in red. Click
the next icon to the right to toggle-off the red circles. Try this on
the invoice60203 tab and you should see a red circle around cell D16.
Formula Auditing is useful but when disallowed entries are copied into
cells, the data-validation rules in these cells are lost and so the red
circles will not appear. This is another security issue. |
There are many other switches, bells and whistles in DVAL,
and now that you’ve come this far it will be easy to experiment. Remember that
the best use of this feature is to guide users and give “on the cell” tips about
what may be entered. But malicious users will easily circumvent DVAL.
Nothing is better than applying this to an example from
your situation, so give Data Validation a try.
About the Author
Joe Kennedy is author of “The Small Business Owners Manual” (www.TSBOM.com),
has over 20 years of experience with small businesses all over the U.S. as
owner, director, manager, employee, vendor, and client. Contact Joe at
joe@tsbom.com. He encourages readers’
comments and ideas.