Highlight the cells
This example shows you how to restrict the data entered to numerical values in a particular range.
Select the cells you want to apply this restriction to. In this case "C3:F7".
In this example we will use a reference to a cell containing the minimum and a formula to calculate the maximum value.
Enter the Criteria
Press (Data > Validation) to display the Data Validation dialog box and select the Settings tab.
Select Decimal from the Allow drop-down box and select "between" in the Data drop-down box.
Select the cell "C2" for the minumum.
Enter the following formula for the maximum: =MAX($J$2:J$7)
The MAX function returns the largest value in a list or array of numbers.
Values can be entered directly into the value box or you can use a formul (including worksheet functions).
These must be entered as absolute values for the range to work correctly every time.
Press OK to apply the data validation.
You can now only enter numeric values between 10 and Max(" ") = 60
There is an exception to this though because dates and times are actually numbers.
It is possible to enter a date provided that its numeric value is within the range specified.
The range of dates that would be accepted are from ?? To ??
To ensure that dates are not displayed the cells must be formatted as a Number (and not as a Date or Time) in the (Format > Cells)(Number tab) dialog box.
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext