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

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

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

[@elischrag8436]

same. Lmk if you've found the answer

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

[@tiger9954]

You’re awesome ❤

[@miltonswl9349]

So helpful thanks buddy

[@darrylmorgan]

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

[@TeachExcel]

Very welcome)

[@marcos4325]

Thanks, very helpful

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

[@General_DDay]

So good! Can this be done in google sheets?

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

[@Redzero062]

Trying to edit my drop down to read the entire table because there's always expanding and decreasing places on my table. Big issue is data validation won't accept my table and only uses the cell as reference. Not sure if it matters but I have my listed data on a separate sheet. Is there something I'm doing wrong or will I just be forced to redo data validation each time I add a value?

[@stewartbrown5333]

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

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

[@DigitalStarLord]

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

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

[@FRANKWHITE1996]

nice explained

[@TeachExcel]

Thanks!!

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

[@Luciano_mp]

Thank you.👍

[@TeachExcel]

no problem)

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

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

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