Particular Range

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.

microsoft excel docs

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.

microsoft excel docs

Press OK to apply the data validation.

Dates Exception

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.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext