Dynamically Update a Drop Down Menu/List - Data Validation & OFFSET() Function

http://www.TeachMsOffice.com
This tutorial will show you how to have a dynamically updated drop down list in excel; how to make your data validation drop down list or menu dynamic. This means that every time you add something to the list the drop down menu will automatically include that item in its list.

This uses the OFFSET() function the COUNT() function and Data Validation in Microsoft Excel.


To get the spreadsheet used here of for more excel video tutorials, tips and tricks, visit the website http://www.TeachMsOffice.com You can also visit the forum there if you have any questions.

Have a great day!

Category:

Education

Tags:

• microsoft
• excel
• data
• validation
• dynamically
• update
• lists
• menus
• text
• offset()
• count()
• functions
• automatically
• quickly

[superbeast326]

I have a question. If I have two named formulas that returns texts according to the cell that they are placed in, how can I make the data validation list use those two formulas?

[ExcelisHell]

@superbeast326 You can use a named range or formula just like you would a normal formula, for the most part. Just make sure you have everything correct in the name and then type something like =name (replacing name with the name of your named range) in the data validation window.

[drawnasunder]

Why not just use a named range?

[ExcelisHell]

@drawnasunder You can use a named range but that won't automatically update the drop-down menu unless you use the same formulas/techniques as shown in this tutorial.

[tonynkh]

Hi, thanks for the video. That's really helpful. Just a quick question, if I rename the items on the list, the renamed name that I have already chosen from the drop down list doesn't update automatically. Is their any way to do this automatically?

[dutchice911]

If you are working with Range Names just replace "A2" with your Range Name. E.g. You have a Range Name called: "TOOLS" your formula would be: "=offset(TOOLS,0,0,counta(A:A)­-1)" --> Without the quotation marks

[superbeast326]

Actually no worries, I have came up with another way to get the results I want.

[superbeast326]

I have two named formulas. They are named HomeTeamMemberName and AwayTeamMemberName. They return the names placed in two seperate ranges. The two formulas return the names based on the cell that the formula is placed in. I want to use those two formulas in Data Validation list, so that the Data Validation list is generated based on the cell that it is placed in. Is this possible?

[arlopeznieto]

Thanks!!, very helpful

[jonnyblogy]

If you stored your data, (saw, hammer, mallet) In a different worksheet could you still source it?

[leanonmyself]

I am using "dynamic data validation" i.e I can "expand" the list. I however have a problem if I want to modify a category which I have been using for a while. (does not apply) If I just "modify" the name of the category from the source list, the name changes in the drop-down list, but all "past work" is still according to the "old" category name. My question is;how can I create a drop-down list which allows for modifications of categories and also changes the my "past" usages of the drop-down?

[ExcelisHell]

@drawnasunder lol. The problem is that it will add a lot of blanks to the drop down menu, as you stated. If you are creating this workbook for yourself, maybe you won't mind those blanks. But, if you want a professional looking worksheet, you will use dynamic ranges or other similar solutions. Your solution will get the job done, but won't be acceptable in most environments; think corporate environments.