Microsoft Office Tutorials and References
In Depth Information
In this case, the reference to cell C1 is an absolute reference; it will not be adjusted for the cells in the selected
range — which is just what you want. The data validation formula for cell A2 looks like this:
The relative cell reference is adjusted, but the absolute cell reference is not.
Data Validation Formula Examples
The following sections contain a few data validation examples that use a formula entered directly into the For-
mula control on the Settings tab of the Data Validation dialog box. These examples help you understand how to
create your own data validation formulas.
All the examples in this section are available at this topic's website. The file is named
data validation examples.xlsx.
Accepting text only
Excel has a data validation option to limit the length of text entered into a cell, but it doesn't have an option to
force text (rather than a number) into a cell. To force a cell or range to accept only text (no values), use the fol-
lowing data validation formula:
This formula assumes that the active cell in the selected range is cell A1.
Accepting a larger value than the previous cell
The following data validation formula enables the user to enter a value only if it's greater than the value in the
cell directly above it:
This formula assumes that A2 is the active cell in the selected range. Note that you can't use this formula for a
cell in row 1.
Accepting nonduplicate entries only
The following data validation formula does not permit the user to make a duplicate entry in the range A1:C20:
This is a logical formula that returns TRUE if the value in the cell occurs only one time in the A1:C20 range.
Otherwise, it returns FALSE, and the Duplicate Entry dialog box is displayed.