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

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

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

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

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

[drawnasunder]

@ExcelisHell Yes it will. If you have the foresight to designate, in advance, the whole column (or any range) as the drop-down's range to pull its options from (using the offset column in the drop-down validation formula), you can much more easily instead define in advance a named range called "Tools", as A2:A50, and then make the drop-down validation list "=Tools".

[drawnasunder]

You'll get a bunch of blanks in the dropdown list, but those are the blanks awaiting the new tools you add later on. As soon as you add them later, they appear as options in the drop-down list. You don't need to dedicate the whole column. And no formulas needed at all. Added bonus: because named ranges work across worksheets, your dropdown menu can be on a different worksheet than the option source list. Works on 2003 thru 2010, maybe older too. Try it - much easier.

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

[20c0260]

You can do the same by just selecting entire column. In source type =A:A.

[ExcelisHell]

@20c0260 That's true but it doesn't work well in older versions of Excel.

[sushant1942]

hi

My selection is=>when i select cell Value in Column A drop down list then cell Value in Column B drop down list show only range/List related with column A cell value.

My problem is

When I change or delete the Column A cell value then Column B value is as per last selection value until i Chang or delete it

I want solution for

when i change or delete the column A value then Column B cell value must be blank until i select it from drop down list.

Thanks in advance for your help

[onyemaechi]

can u not add a new value straight to the drop down list instead?

[Kozlo922]

I'm no novice to Excel but I do appreciate your explanations of the reasons of the zeros and -1 use in the offset formula. Nice job.

[wixch]

i want to create a drop down box in excel for the months 1-12, i then want the dropdown selection (ie. march) to be able to generate d-mmm-yy for all 31 days and have the day (ie mon tues wednes) in the next column

so,

A1 | Jan-Dec dropdown

B1-31 | d-mmm-yy

C1-31 | dddd

please help me do this

[nfhu88]

is there a way to have your list on a different sheet and still be able to dynamically update? im having alittle trouble creating the formula.

[lcfw23]

Hi, great video - you obviously know your stuff! I wanted to ask if you have a link to a video for bringing in product codes and prices? Im not entirely sure what to search for and cant crack it myself. Any pointers would be great! Thanks.

[mrkva4]

hi, how can I make work update drop list if my list is on 2nd sheet in same document, and must drop on the 1st sheet ? Thank you.

[bspace]

What happens if the values that you want to appear in the drop down box are on another worksheet?

[MrCarricarte]

Nothing works the same, but all references has to be with the name of the worksheet

[vmistry94]

Whenever I try and select different values (by clicking ctrl) the data validation doesn't let me accept the values, it either has to be in a straight down list or values entered yourself, is there a way to select different cells and put them inside a drop down list?

[ryeweeds]

ei,

thanks for this. i can use this one.

[7shami]

make the product list as a table it will auto add on validation list

[ExcelisHell]

That's true, but the problem with making it a table is that it is not as versatile as using data validation.

[tirathmistry]

Awesome video tutorial ExcelisHell!!

I learn a lot with help of your all powerful & useful tutorial.

Your detail explanation is also awesome. Please upload more excel video tutorial

Thanks so much for your kind effort for making useful excel tutorial video.

Thanks with Happy & prosperous New Year :-)

[ExcelisHell]

Thanks, I'm glad you like them:)

[birdyflu]

what if you have two or more of the same reference?

[ExcelisHell]

It will include both of them.