PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
Data validation means to check the data before entry. It gives us the power to condition the values which can be entered and rejected into any cell.
The data validation is a very important aspect of any data filling job. Many a times we have fields which have limited type of entries and which can’t be given to the user for manual entry.
PROBLEM WITH MANUAL ENTRY
If we have to work on the manual entries after the data has been filled , it can cause so much trouble for us if the data is not in the perfect format. If we keep the field just open for the entry, if there are a number of people filling up that data , there are bright chances of format problems.
Now imagine if we have a 1000 rows and there are slight differences in the format of the entry, no matter it was done by different people, we ‘ll never be able to work on that and we might end up in correcting all the data wasting our time and resources.
SO, FOR SUCH CASES WE ALWAYS PREFER CREATING A LIST FOR A FIRM FORMAT AND CONTROLLING THE ENTRY OF THE DATA.
This was an excerpt from the previous post “HOW TO INSERT A DROP DOWN LIST IN EXCEL” which is applicable for the current case too.
Now let us understand the case of dependent drop down list.
The simple drop down list lets us choose the options from a given list. But think of a better scenario, where we have a list which will populate the options on the basis of the CHOICE MADE IN THE FIRST LIST.
This is how we can CREATE REALLY INTELLIGENT REPORTS by giving the only applicable choices.
In this article, we’ll discuss how we can create the dependent drop down lists.
We started with selecting the DATA VALIDATION OPTION from the DATA VALIDATION button under the DATA TAB.
Put the data in different lists so that they are easy to manage.
The data lists can be kept on the same page of the drop down lists, but it is better if we keep them on the other sheet so that we might not mess with them by mistake.
Look at the picture below, how we have organized the data.
To create the dependent drop down list we need to create THE NAME RANGES of the country lists by the name of the continents. Follow the steps to create ranges with the continent names.
The following animation has been given for the reference.
Select the cell under the COUNTRIES on the first sheet where we want to populate the list.
Go to DATA VALIDATION under the DATA TAB and choose DATA VALIDATION.
Select LIST from the DROP DOWN.
Refer picture below.
The following picture shows the running of the example.
We choose the CONTINENT and corresponding COUNTRIES get populated into the list.
We can choose any country from these given choices.
We can see that the example is running as per expectations.
DAY OF THE WEEK |
SUNDAY-MONDAY |
TUESDAY-FRIDAY |
SATURDAY |
ACTIVITIES | ||
SUNDAY-MONDAY | TUESDAY-FRIDAY | SATURDAY |
SURFING | WALL CLIMBING | CYCLING |
SWIMMING | SKATING | WALKING |
So, we create a simple list for the days entries.
Created the ranges of all the Activities by selecting the complete columns as shown in the picture.
We select the cell and start to create a list by going to data validation option under Data tab.
We choose the list and come to the source.
Here, we see that we don’t have space but a hyphen (-).
So we put the following formula in the source
=INDIRECT(SUBSTITUTE(C6,”-“,”_”))
It firstly, substitutes the – with an _ and then feeds the result to the Indirect.
C6 contains the name of the week section i.e. the basic list.
Indirect populates the complete list of activities into the ACTIVITIES COLUMN as per the selection in CHOOSE THE DAY.
The program behaves as per expectations.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE