HOW TO CREATE A DYNAMIC DROP DOWN LIST IN EXCEL?

INTRODUCTION

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.

The second was the case of dependent drop down list.[CLICK HERE TO KNOW HOW TO INSERT A 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.

Now, one more scenario is there. Suppose we have a dynamic data. The data keeps changing .It means that every time we need to change the data and create a new drop down list every time in both the cases discussed. Be it simple drop down list or the dependent drop down list, if we add some new entries, the complete procedure needs to be repeated.

How would it be, if we could do something which would save us from repeating the complete procedure. So, we ‘ll create a solution for that which we call as DYNAMIC DROP DOWN LIST IN EXCEL.

The dynamic word is used because the data can be changed just by adding it to the already present data.

In this article, we will learn to create a dynamic drop down list in Excel.

UNDERSTANDING THE CONCEPT OF DYNAMIC DROP DOWN LIST

The standard procedure of creating a drop down list is as follows.

1. We have a data listed.

2. We put this list into the DROP DOWN list after giving it a name.

So, technically we need a way to add our new data into the same NAME GROUP of items.

So we can do it in many ways, but here we would discuss TWO FASTEST WAYS of creating a dynamic drop down list.

USING THE TABLES [FASTEST WAY]
USING THE OFFSET FUNCTION.

For the example, we’ll take a scenario.

Suppose we have a list of the students enrolled into a class and the admissions are going on.

We need to create a list, which we will create once and the new student addition will take place automatically into the list, just by updating the master data in the fields.We start with two students.The already admitted students are

Steve and Dave.

STEP 1:CREATE A TABLE FOR THE DATA

Follow the steps.

Create a table with the header as STUDENT NAMES.
Insert the data as STEVE and DAVE in the table.
Name the table as student_names.
The complete process is shown in the picture below.

STEP 2:CREATE THE DYNAMIC DROP DOWN LIST

Follow the steps to create the dynamic drop down list in excel.

Let us store our data in a sheet named MASTER SHEET.
We’ll create the dynamic drop down list in other sheet and name it FORM or anything you want.
In the form, we have kept just one field for now.

STEPS[CONTD..]

REFER PICTURE BELOW

Go to the sheet FORM and place the cursor in cell H7 [as per the example. yours can be different]
Go to the DATA TAB > DATA VALIDATION.
From the DIALOG BOX , choose from the ALLOW DROP DOWN LIST, LIST option and put the following formula in the SOURCE. =INDIRECT(“student_names”).
Indirect function refers to the location mentioned as a text .
So by passing this function , we are telling the data validation that the source of the data is the table named student_names.
As soon as we click ok, our dynamic dependent list is ready. Check it by pressing the drop down.

**CREATE DROPDOWN LIST FROM THE DATA VALIDATION**

Now, that our drop down list is ready, its time to check if its really dynamic or not.Just add the names in the table but mind that there is not gap.

IN FACT , WE ARE TAKING ADVANTAGE OF A STICKY ABILITY OF THE TABLE.

TABLE INCLUDES THE DATA INTO ITSELF WHEN WE WRITE SOMETHING IN ITS ADJACENT CELLS.

By this, our list becomes dynamic too.Look at the following animation to see, how our dynamic drop down behaves.

For the example, we’ll take a scenario.Let us take an example of the countries going to attend a submit.We’ll maintain the data in a sheet named MASTER SHEET.The list will be created in a sheet named FORM.

For the example, we will just demonstrate the input into the drop down list as we update the data in the master list.United States and France have already acknowledged.

Let us find out , how we can do this using the OFFSET FUNCTION.

But before that let us check out the OFFSET FUNCTION. Let us just check out what it does briefly.

OFFSET FUNCTION returns the reference of the cell or range specified at a specified offset/cells away from the current cell.

=OFFSET ( REFERENCE , ROWS , COLUMNS, HEIGHT, WIDTH )

Reference is the reference from which we’d try to find the offset. It is the pivot of the function relative to which we’d find out the new reference.

Rows is the number of rows you want to offset from the reference. A positive number tells that these are below the reference whereas negative number tells that these are above the reference.

Cols is the number of columns we want to offset from the reference. It is again just like the graph. cols value 2 means 2 columns to the right and -2 means , 2 columns to the left.

Height is the height, in number of rows, that you want the returned reference to be. Height must be a positive number.

Width is the width, in number of columns, that you want the returned reference to be. Width must be a positive number.We have a complete post with example about the OFFSET FUNCTION HERE.

STEP 1:CREATE A MASTER SHEET WITH THE DATA

Follow the steps.Create a MASTER SHEET containing the different columns of the data.In our example we have the COUNTRY NAMES. Only two names are available with us right now.So let us create one column named COUNTRY NAMES and insert the country names UNITED STATES and FRANCE.

**CREATE A MASTERSHEET WITH DATA COLUMNS**

STEP 2:CREATE THE DYNAMIC DROP DOWN LIST BY USING THE FUNCTION OFFSET

Follow the steps to create the dynamic drop down list in excel.

Let us store our data in a sheet named MASTER SHEET(2).
We’ll create the dynamic drop down list in other sheet and name it FORM(2) or anything you want.
In the form, we have kept just one field for now.

STEPS[CONTD..]

REFER PICTURE BELOW

Go to the sheet FORM and place the cursor in cell H7 [as per the example. yours can be different]
Go to the DATA TAB > DATA VALIDATION.
From the DIALOG BOX , choose from the ALLOW DROP DOWN LIST, LIST option and put the following formula in the SOURCE
=OFFSET(‘MASTER SHEET (2)’!$D$5,0,0,COUNTA(‘MASTER SHEET (2)’!$D:$D),1)
As soon as we click ok, our dynamic dependent list is ready. Check it by pressing the drop down.

EXPLANATION OF THE FUNCTION USED

We used the function for the source as

=OFFSET(‘MASTER SHEET (2)’!$D$5,0,0,COUNTA(‘MASTER SHEET (2)’!$D:$D),1)

Let us try to understand the function.

The first argument is ‘MASTER SHEET(2)’!$D$5 which refers to the D5 cell of the sheet MASTERSHEET (2).

It is the argument which is the reference for the function.

The second and third arguments are the offsets of rows and columns which are 0 and 0 as we want to start our calculation from the reference itself.Fourth argument is the COUNTA( D COLUMN OF THE SHEET MASTER SHEET(2))

The COUNTA FUNCTION will count all the filled cells in the column D below D5 and return the number as the height of the returning range from the offset function.

The last argument is the width of the returning range which is 1 as we have just one column.

So, summarizing, OFFSET FUNCTION returns the range of all the names below the D5 cell in the D column and feeds it to the dynamic drop down list. Now, that our drop down list is ready, its time to check if its really dynamic or not.

We’ll add various other names in the country list who are going to attend the conference. Look at the following animation to see, how our dynamic drop down behaves.

INTRODUCTION