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.
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.
REFER PICTURE BELOW
=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.
REFER PICTURE BELOW
We’ll add various other names in the country list who are going to attend the conference.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE