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

VBA/Macros Course (40% Discount): www.teachexcel.com/vba-course...
Excel File: www.teachexcel.com/excel-tuto...
Excel Forum: www.teachexcel.com/talk/micro...
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!
TeachExcel.com
#excel #tutorial #howto

[@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.

[@AriefAsakura]

this is always my question,
and still got no answer to this day

[@wayneedmondson1065]

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!

[@TeachExcel]

Thanks for the tips!! I am sure that they will benefit many, including me haha. I didn't know Excel would update the range in data validation if it points to a table range without the other naming step, big kudos for that tip! It turns out there are about a million different ways to make dynamic ranges these days and I find that kind of fun; in the comments on this video alone there are two alternatives to the offset function mentioned even - maybe it would be fun to make a video of all the ways to do it.

[@sanjeebbhoi9781]

❤😊 I ❤❤❤❤❤❤❤😊❤😊❤😊❤❤❤❤❤❤❤❤❤❤❤

[@marcos4325]

Thanks, very helpful

[@miltonswl9349]

So helpful thanks buddy

[@sherryfox227]

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???

[@varadkale6639]

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.

[@abrahameninla3615]

I tried this method too and it's Ok. You can use the two formulas. Thanks, guys.

[@TeachExcel]

There are so many interesting ways to make a dynamic range, thanks for this input!)

[@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

[@consciousbean7520]

I am searching for this too. Have you found a solution?

[@robdesti6576]

@@consciousbean7520 Just found out: ctrl+f and replace all your entries manually if possible. Only way I currently know how.

[@AriefAsakura]

@@robdesti6576 sad :(

[@darrylmorgan]

Cool Tutorial!Thank You Sir :):):)

[@TeachExcel]

Very welcome)

[@Luciano_mp]

Thank you.👍

[@TeachExcel]

no problem)

[@surendrareddy2375]

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.

[@WillsBacklog]

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

[@General_DDay]

So good! Can this be done in google sheets?

[@stewartbrown5333]

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

[@FRANKWHITE1996]

nice explained

[@TeachExcel]

Thanks!!

[@richardkalaw4892]

Hi, is this applicable to excel 365?

[@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.

[@TeachExcel]

Thanks for the note!

[@fulinsun9056]

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?

[@TeachExcel]

Seems like a problem maybe with how it is all connected. Upload a sample file along with your question to our forum and it will be easy to see whats up. TeachExcel Forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment

[@rollingthunder4599]

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

[@iresolvers]

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

[@kathyroberts7777]

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.

[@kprotube2352]

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