Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to create dynamic reference table name in Excel
  • Conditional Formatting Data bars Examples in Excel
  • How to do a t-Test in Excel?
  • Conditional Formatting Icon Sets Examples in Excel
  • Conflicting Multiple Conditional Formatting Rules in Excel

References

  • Left Lookup in Excel
  • Offset in Excel
  • How to get address of named range in Excel
  • How to use Excel MATCH Function
  • How to use Excel MMULT Function

Data Validations

  • Excel Data validation require unique number
  • Excel Data validation only dates between
  • Excel Data validation no punctuation
  • Excel Data validation date in specific year
  • Excel Data validation date in next 30 days

Excel Data validation with conditional list

by

How to create a drop-down and multiple dependent drop-down lists.in Excel

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.

Formula

=IF(A1="See full list",long_list,short_list)

Explanation

In the example shown above, 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.

How this formula works

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.

 

 

 

Post navigation

Previous Post:

VLOOKUP with two client rates in Excel

Next Post:

How to Create Gantt Chart in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Not Equal To ‘<>‘ operator in Excel
  • IF with boolean logic in Excel
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • XOR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • DAY function: Description, Usage, Syntax, Examples and Explanation
  • Count times in a specific range in Excel
  • How to calculate quarter from date in Excel
  • Convert decimal hours to Excel time
  • Get last day of month in Excel

Grouping

  • How to randomly assign people to groups in Excel
  • Group numbers with VLOOKUP in Excel
  • If cell contains one of many things in Excel
  • Running count group by n size in Excel
  • Categorize text with keywords in Excel

General

  • How to test a range for numbers in Excel
  • How to get random value from list or table in Excel
  • List sheet names with formula in Excel
  • Currency vs Accounting Format in Excel
  • How to get original number from percent change in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning