Data Validation List With Condition | Using Formula

Contact Us

Bhandup (W): Dreams Mall, Opp BMC Market, Nr Bhandup Station, Mumabi 400078.
Thane (W): G-03, SAI Sagar App, Beside Sai Babab Mandir, Kalher Thane.

+91 9870419968

info@advancedexcelcourse.com





Data Validation List With Condition.


Data validation : As the name says data validation, it helps user validate the data that we want to put in a perticular cell or a rang of cells. In a Data Validation window we could see a sub window tab :

Settings :

Help us set a validation criteria.

Lets understant what is data validation list and how to use it with condition.

Open a new workbook choose Sheet2, copy and past the below table in cell A1:

×
A B C
1 SPORTS Country Players
2 Football Brazil Pele
3 Cricket Portugal Ronaldo
4 Hockey Greece Cristiano
5 India Ricardo
6 SriLanka Giorgos
7 Bangladesh Antonios
8 Russia ViratKohli
9 Denmark KapilDev
10 Sweden Sangakkara
11 Jayawardene
12 TamimIqbal
13 ShakibHasan
14 SlavaFetisov
15 PavelBure
16 PoulPopiel
17 LarsEller
18 Alfredsson
19 NicklasL
20



Now go back to sheet1 and type:

A B C
1 List of Sports Sport Playing Country Players for the Country
2
3
4
5

  1. From Sheet1 select cell range A2:A5
  2. Go to Data Validation - Settings - Under Allow cloose List from dropdown
  3. Click under source and then selct the range A2:A4 from sheet2 and hit ok
  4. From Sheet1 select cell range B2:B5
  5. Go to Data Validation - Settings - Under Allow cloose List from dropdown
  6. Click under source and then selct the range B2:B10 from sheet2 and hit ok
  7. From Sheet1 select cell range C2:C5
  8. Go to Data Validation - Settings - Under Allow cloose List from dropdown
  9. Click under source and then selct the range C2:C19 from sheet2 and hit ok

Now if we select any cell from range A2:C5 we could find a dropdown option that could help us select the unique data.

In the Advanced option will understand how to get these dropdown with condition

We learn how to get the data validation list, now let understand how to get these lists based on condition.
Let's Say:
If I select Cricket from List of Sports
- then in Sports Playing Country I should only get the list of countries that play Cricket
- And in Players for the Country I should only get the players for that particular country.

Follow the Example below to understand how we could perform this task:

×
A B C D
1 SPORTS Football Cricket Hockey
2 Football Brazil India Russia
3 Cricket Greece SriLanka Denmark
4 Hockey Portugal Bangladesh Sweden
5
6 Brazil Greece Portugal
7 Pele Giorgos Cristiano
8 Ronaldo Antonios Ricardo
9
10 India SriLanka Bangladesh
11 Virat Kohli Sangakkara Tamim Iqbal
12 Kapil Dev Jayawardene Shakib Hasan
13
14 Russia Denmark Sweden
15 Slava Fetisov Poul Popiel Alfredsson
16 Pavel Bure Lars Eller Nicklas L
17


  1. Arrange the data in proper order and name them as per their categories (Title). Follow the link above to get a data arranged in a proper order.
  2. Open a new workbook choose Sheet2, copy and paste the above table in cell A1
  3. Now go back to Sheet1 and type:
  4. A B C
    1 List of Sports Sport Playing Country Players for the Country
    2
    3
    4
    5

  5. From Sheet1 select cell range A2:A5
  6. Go to Data Validation - Settings - Under Allow cloose List from dropdown
  7. Under source type =Sports and hit ok
  8. From Sheet1 select cell range B2:B5
  9. Go to Data Validation - Settings - Under Allow cloose List from dropdown
  10. Under source type =IF(A2="Football",Football,IF(A2="Cricket",Cricket,IF(A2="Hockey",Hockey,"No Country Found"))) and hit ok
  11. From Sheet1 select cell range C2:C5
  12. Go to Data Validation - Settings - Under Allow cloose List from dropdown
  13. Under source type =IF(B2="Brazil",Brazil,IF(B2="Greece",Greece,IF(B2="Portugal",Portugal,IF(B2="India",India,IF(B2="SriLanka",SriLanka,IF(B2="Bangladesh",Bangladesh,IF(B2="Russia",Russia,IF(B2="Denmark",Denmark,IF(B2="Sweden",Sweden,"No Players Found"))))))))) and hit ok

Now if we select any cell from range A2:C5 we could find a dropdown option with condition that could help us select the data from a unique List.

NOTE :
  • If you are not aware of "IF Function and Name Range" then kindly click on the appropriate link:
  • There are many other ways to get similar output, follow us in order to stay updated

Input-Message:

Help us set a Input-Message once the cell is selected, so that user can understand what he need to feed in.

A B C
1 List of Sports Sport Playing Country Players for the Country
2
3
4
5

Now since we have set validation criteria, let’s put an input message in a range of cells so that user can understand the appropriate condition.

  1. Select the cell B2:B5
  2. Go to Data Validation - Input-Message.
  3. Ensure that : Show input message when cell is selected is checked
  4. Under Title type : Sport Playing Country
  5. Under Input message type: Select only the Countries that are listed in the dropdown list
  6. Select the cell C2:C5 and repeat the step 2 and 3
  7. Under Title type : Players for the Country
  8. Under Input message type: Select only the Players that are listed in the dropdown list and hit ok

Now if you select any cell from range B2:C5 you will see the Input message we have putin

Error-Alert

Help us set a Error Alert, if a user try to put a data other than the criteria set.

A B C
1 List of Sports Sport Playing Country Players for the Country
2
3
4
5

Now since we have set validation criteria and Input Message, let's put an Error-Alert in a range of cells so that user can understand Error-Alert category, if he tries to put any other data apart from the data present in a drop down list.

  1. Select the cell B2:B5
  2. Go to Data Validation - Error Alert.
  3. Ensure that : Show error alart after invalid data is entered, is checked
  4. From Style drop down choose anyone style
  5. Under Title type : Sport Playing Country
  6. Under Error message type: Select only the Countries that are listed in the dropdown list
  7. Select the cell C2:C5 and repeat the step 2, 3 and 4
  8. Under Title type : Players for the Country
  9. Under Error message type: Select only the Players that are listed in the dropdown list and hit ok

Now if you try to put any other data apart from the data present in a drop down list you get Error Alert that we have putin






  • facebook
  • google+
  • Youtube
  • skype
  • whatsapp
  • mobile
  • mail