Validating drop down
This is done using the INDIRECT function (Learn more awesome examples of the INDIRECT function).
A common issue when creating dependent lists is the use of illegitimate characters in named ranges.
This is a guest post by Alan Murray from Computergaga. It often goes unnoticed as Excel users are eager to learn the highs of Pivot Tables, charts and formulas.
It controls what can be input into a cell, to ensure its accuracy and consistency. In this blog post we will explore 11 useful examples of what Data validation can do.
You cannot begin named ranges with a number, or use spaces and some other symbols.
So, if items in your list use spaces, or start with numbers it presents an obstacle.
To apply these Data Validation rules; You may need to ensure that data is entered in uppercase, such as this example of UK postcodes being entered.
The cells need to accept the entry of both text and numbers, but the text must be uppercase. The EXACT function is used to compare the cell entry with the uppercase version to see if they are the same. For this example, the validation was applied to range A2: A6.
For the dependent list in cell G2, the selection in cell F2 needs to be converted into a reference to the named ranges.
There are many techniques for identifying and removing duplicates, but it would be better if you could prevent them in the first place.
By using the COUNTIF function in a custom formula we can.
When you need a simple list such as Open and Closed, or Yes and No, then typing the entries in makes sense.
When you need a more dynamic list for items that change over time such as lists of products, places and people, then referring to a range makes sense.
Search for validating drop down:
The formula below counts the occurrences of the inputted value in the range A2: A8.