Aleja Quiambao
Grade 9-SSP
COMPUTER PROGRAMMING
3rd Grading
1. What is Data Validation?
Data validation is a feature in Excel used to control what a user can enter into a cell.
For example, you could use data validation to make sure a value is a number between 1 and
6, make sure a date occurs in the next 30 days, or make sure a text entry is less than 25
characters.
2. How to validate 2 drop down menu?
Creating a Dependent Drop Down List in Excel
1. Select the cell where you want the first (main) drop down list.
2. Go to Data –> Data Validation. ...
3. In the data validation dialog box, within the settings tab, select List.
4. In Source field, specify the range that contains the items that are to be shown in the
first drop down list.
5. Click OK.
3. How to validate numbers using greater than or less than?
Greater than
The greater than operator (>) returns TRUE if the first value is greater than the second
value.
1. For example, take a look at the formula in cell C1 below.
Explanation: The formula returns TRUE because the value in cell A1 is greater than the
value in cell B1.
2. The OR function below uses the greater than operator.
Explanation: This OR function returns TRUE if at least one value is greater than 50, else
it returns FALSE.
Less than
The less than operator (<) returns TRUE if the first value is less than the second value.
1. For example, take a look at the formula in cell C1 below.
Explanation: the formula returns TRUE because the value in cell A1 is less than the
value in cell B1.
2. The AND function below uses the less than operator.
Explanation: this AND function returns TRUE if both values are less than 80, else it
returns FALSE.
4. Give sample of data validation.
“Data validation with conditional list”
Generic formula
= IF (A1="See full list",long_list,short_list)
Summary
To allow a user to switch between two or more lists, you can use the IF
function to test for a value and conditionally return a list of values based on the
result. In the example shown, the data validation applied to C4 is:
= IF (C4="See full list",long_list,short_list)
This allows a user to select a city from a short list of options by default, but
also provides an easy way to view and select a city from a longer list of cities.
Note: I ran into this formula and approach on the excellent Chandoo site.
Explanation
Data validation rules are triggered when a user adds or changes a cell value.
This formula takes advantage of this behavior to provide a clever way for the user
to switch between a short list of cities and a longer list of cities.
In this formula, the IF function is configured to test the value in cell C4. When
C4 is empty or contains any value except "See full list", the user sees a short list of
cities, provided in the named range short_list (E6:E13):
If the value in C4 is "See full list", the user sees the long list of cities,
provided in the named range long_list (G6:G35):
The named ranges used in the formula are not required, but they make the
formula a lot easier to read and understand. If you are new to named ranges, this
page provides a good overview.
Dependent dropdown lists
Expanding on the example above, you can create multiple dependent
dropdown lists. For example, a user selects an item type of "fruit", so they next see
a list of fruits to select. If they first select "vegetable" they then see a list of
vegetables. Click the image below for instructions and examples:
