Hello, everybody, in the context of data validation drop down list, I have a question. Let's assume that you had created a drop down list from the source given a two, a three and a four. Now you had used this particular drop down in couple of cells, and those are scattered across different parts of the workbook. Now, let's assume that there is a fourth parameter that needs to be added, and that's called not submitted. Now, by default, all these drop down lists which are scattered will not exhibit that the fourth option does exist, it will not even show that not submitted, how to ensure that using a very simple trick, I'm able to make a universal change that is wherever this particular drop down was used. It should now also show you not submitted as the fourth option.
Well, to be able to execute that you must use their name concept range naming concept. That is that we go back one step, let me choose These t says, and towards the top left corner of the screen which says Name box. Let me select that selection and write a name called list name, I must press enter, so as to ensure the name has been registered. Now, let me go back to the cell with which has a do drop down list. I go to data, data validation, data validation. Okay, now, as we noticed from the previous videos we are seeing allow a list and we have chosen manually this range.
Now this time, I am going to write equal to equal to is a must list name. It's not case sensitive the name, but yes, the spelling should be correct. So list name, either written in lowercase or uppercase doesn't matter. As I press OK, notice it still has the three values list name, let me copy paste this couple of times. I did that. Now let's assume there is one more value that got added to This input list, but currently, list name just points to these three cells.
So what I do, I go to formula, I go to Name Manager, something that you may have seen in one of the earlier videos. Now what I will do is, I will pick up the list name, and I'm going to edit that. Why would I want to edit because in the earlier definition, it only took three values. This time, I am going to delete that and choose the extra value just including all the four values. Now the advantage is, as soon as I press OK, since all the drop down list under the data validation had referred to equal to list name, and therefore when I use one of these drop downs, notice it says not submitted, not submitted. It's a universal change that has happened simultaneously across the entire workbook.
So amongst all approaches that you've seen in this video, and the last video, the best approach so far is using a named range. And the other advantages in 2007 version of Excel, if you had tried to create a drop down list using normal procedure data drop down validation allow a list and in the source had you try to go to the other sheet it would not have allowed you to select this from the other worksheet, this is a problem in 2007 version of Excel. So, practically speaking in 2007 version of Excel, you cannot refer to a list name which is in the other worksheet. So, to circumvent that problem, you may as well name that as list name and then refer that name equal to list name. So, I am going to do the same thing equal to list name and I'll press Enter or okay and as I can see, I can get all the four values which I had named as list name.
So this was data validation list along with range naming