3 Ways to Automatically Update Drop Down Lists in Excel - Data Validation

Ensure that your Data Validation Drop Down Lists or Menus are automatically updated with any new entries or deletions - these automatic methods make maintaining your spreadsheet much easier.

The 3 methods that I show you here will cover the 3 most common ways to have your data validation lists automatically update:

- a robust and versatile method (the one you should use)
- a very commonly taught method, though with some downfalls (also mentioned here)
- a formula that allows bypassing some of the steps mentioned in the previous methods

These 3 methods should give you a complete understanding of how to automatically update your lists, regardless of the setup of your spreadsheets.

I hope you find this tutorial helpful!)

Enjoy!

#excel #tutorial #howto

[wayneedmondson]

Hi.. thanks for the video. Here is a trick.. when using a table.. in the Source field of Data Validation, enter the range using cell coordinates, as in: =$A$2:$A$5 (meaning do not use Table Formula Nomenclature). You can type it in manually or paint it in via the cell pointer. If you do this, Data Validation will accept it as a valid range and it will dynamically expand or contract as you add or remove rows from the table. Doing it this way, you don't need to name the range or use INDIRECT and you don't need OFFSET. Also, for the formula example, you can use: =A2:INDEX(A:A, COUNTA(A:A)) as a named range and use the named range as the Source (does not require INDIRECT). For workbooks with high calculation overhead, this avoids use of the OFFSET function which is volatile. Thanks for the videos.. always learn something new. Thumbs up for TeachExcel!

[TelstarTAK]

If you rename list entries in the table, how do you get the cells referring to the list to update/refresh the entries? Thanks.

[Luijeee]

thanks for this. Is there a way push updates to the table through to the data that is referring to the list?
for example you rename Orange to Tangerine, and want all instances of Orange to be replaced with Tangerine

[varadkale]

Didn't know about OFFSET function, but used something like this =Indirect("A2:A"&countA(A:A))
for creating a dynamic drop down with data in column A where A1 contains column name.

[atananmathful]

The offset function is can use only when you have one drop down list, but if you have two or more drop down list that related to each other that you need to use "indirect function " you can't use offset but table is ok.

[fulinsun]

Hi I have a question: Say I have made some progress to my list, Tom - Apple and Jerry - Apple; but now I want to change(in the table) to "AppleBig" and "AppleSmall", the list I already made will not update automatically. Any fix for this?

[surendrareddy]

Hi, to the table created this way, how can we make the drop down list select multiple values. This way I achive 1. dynamically grow the table. 2. have multiple entries selected in each cell.

[General_DDay]

So good! Can this be done in google sheets?

[WillsBacklog]

A very helpful tutorial. Thank you! You saved my gray hair from becoming... grayer :)

[rollingthunder]

First example. What if you want to be able to do this in every cell in a column, not just one cell????

[sherryfox]

Great tutorial. But I have a Dependent dropdown. Plus since it is being used by other with only basic Excel skills, I was trying to use the UNIQUE function to always have an updated list. But I have found there appears to be issues combining UNIQUE lists with offset named ranges and using dependent dropdowns. Is there a way to combine these???

[stewartbrown]

@TeachExcel Do you do personal consultations? I'm in need of some help and cannot find the exact concern I have on Youtube?

[iresolvers]

My Excel 2019 offset command will not work I'm so frustrated

[kprotube]

Food Video.

But i want to make a point.. i guess even if we just use " = " and connect the required cell values, we are gonna get the same thing with '=' sign also .. so why complicate using formula.
A B C D E
1 Apple =A1
2 mango =A2
3
4
Here we will get the same result

what i was looking was the filtered list. like if i have same text multiple times in a column, i wanted to create the filtered list (text used only once) so that i can use COUNTIF function to get total number value of a particular item.

Example :; if Apples are there in the column 'n' times, mangoes 'm' times, using COUNTIF function i should get the total count.

I was looking filter List like we get in the Dropdown Column Filter.



Request Suggest

[kathyroberts]

Was following okay until you got to the last example. Whoa, if I got into your head I would be scared, if you got into mine you would be lonely. I will stick to the first example thank you, I have used this before but you gave me some extra pointers that will be useful. Thank you very much.