Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Dynamic Drop Down List

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More


I have to create a dynamic drop down list that takes the values from another excel file. The problem is that the values are the names of the columns and that the number of the columns change from month to month.

Any ideas how I can do this?
Thank you!

View Answers     

Similar Excel Tutorials

Dynamic Formulas that Update When you Add Data in Excel
I'll show you how to make formulas and functions that automatically update when more data is added to a range in E ...
Make a Drop Down List Menu in a Cell in Excel
I'll show you how to make a drop down list menu in a cell in Excel.  This allows you to have a list appear when yo ...
Dynamic Charts in Excel - The Easy Way
How to create dynamic charts that update automatically when new data is added to Excel. This method uses the table ...
Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a great little macro that allo ...

Helpful Excel Macros

Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
- Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a great little macro that allows yo
Create a 12 Month Calendar With The Current Day Highlighted in Excel
- Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel
Print Specific Pages in Excel
- This free Excel macro allows you to print a pre-specified selection of pages from Excel. This means you can print 2 pag
Get Values from a Chart
- This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt

Similar Topics

Good day,

Wondering if anyone can guide me in a right direction.

I have a list of names and birthdays (a huge list). I want to add a drop down box with 12 months in it so that people could select a month from a drop down and the spreadsheet would display ONLY the birthdays and names for that month.

I don't have a problem creating a drop down w data validation but I am not sure what dynamic formula to use for my data so that it would display only the records according to drop down selection.

I know I could use sort and filter functions but my co-workers dont use excel and i want to create a simple solution where they can simply click one cell, select the month and see the sorted results...

Hi all,

I am working on this excel file and need to create a drop down list. It should have the following features:

1) It should show only unique values
2) it should have the auto-complete text option
3) it should be dynamic, ie when more data is added in column A, it should get added to this dropdown automatically

I know that to have autocomplete function on, i must use control toolbox => combo box.

my main problem is that with that, i cannot use a list name as the source (like in data validation) and i also cannot use the offset function for having a dynamic list.

Please advise me a way to have all 3 features working in the drop down as I have search and search but found ntn

Thanks a ton!

I am stuck. I am trying to create a Dynamic dropdown list. I have followed several tutorials but can't seem to get it to work. I have a named range called actual list which is in R1 & S1 (week & month). I have a named range called week and a named range called month. If I put the actual values in name range called week and month it works however it isn't dynamic. so to make it dynamic I am trying to use Offsets however i dont really understand how to use it properly. for range month my formula is : =OFFSET(DBoard!$S$1,1,0,COUNTA(DBoard!$S:$S),1) ---in S1 is the name month. What am I doing wrong? Also when I click on the formula in the named range it highlights the correct cells however when I go to click on the dropdown it doesnt drop down.



I have a dynamic drop down validation List, which comes from another
spreadsheet list and have a few random blank cells in it. I need to
drop these blank values from the List without making any change in the
parent list. I may also have some duplicate values in the List and need
to drop them too.

Please help.



Hello, everyone -

Over the last couple of days, I have noticed Excel doing something really strange with a bubble chart I have been working on. The chart is based on dynamic named ranges I have set up for the X-, Y- and size values. In turn, different sets of x,y and z data are dictated by a different choices provided in a drop-down list (set up through as a "validation" drop-down list in a cell).

The problem I seem to be having is really weird. The chart will dynamically update when I select a different option in the drop-down list. However, it seems to arbitrarily use the observation number instead of the X-value number for the horizontal axis. For example, if observation # 6 corresponds to the values (3.152, 7.6, 120), the chart will plot this as (6, 7.6, 120) instead! It also seems to do this arbitrarily for some subsets of xyz values (as chosen by the drop-down menu) and not others.

I've checked and double-checked everything I could think of regarding this error - the dynamic ranges are updating correctly. In fact, I have tried manually inserting a chart using explicity cell references, WITHOUT the dynamic ranges, and this does not solve the problem. Removing rows with any blank cells does not help, either.

I'm stuck, and would greatly appreciate your help. FYI, I'm using Excel 2003.

Has anyone out there encountered a similar problem, and, if so, how did you solve it?

Thanks much!

Hi Everyone,

I'm hoping someone knows a simple solution for my problem.
What is the best way to create a list for a drop down box on sheet1 from data on sheet2?

I have two worksheets in a workbook, users fill out a check list and select values from various data validation drop downs in sheet1, that data is pushed over and stored in sheet2. Currently, the sources for all the data validation lists on sheet1 are hidden in sheet1. But now I need to create two dynamic list that pull from two columns on sheet2 and update each time a user adds a new value in sheet1 to add the new value to the list for the next user on sheet1?

I tried adding =OFFSET(Data!G1,1,0,COUNTA(Data!G:G),1) as my data validation source but, excel doesn't like that to much.

Thanks Rob,

Ok, I am not an excel programmer. That being said... I have a dynamic drop-down list with names in it. I have worksheets that match the names within that list. I want to be able to select one of those names from the drop down and pull the data from the corresponding worksheet that is named the same as the entry in the list. I am truly at a stopping point until I figure this out. Please help if you can.

I have a macro enabled excel file. I have a button in sheet2. When i click on this button, a drop down(drop down 1) is created dynamically and is placed in A1 of sheet1. When i select a value from this drop down(drop down 1), a value change listener is invoked inside which a drop down(drop down 2) is created,values are populated and this drop down is then placed in cell A2 of sheet1. My requirement is:

1. Right now i am creating a drop down whenever there is a value change in drop down 1 and am placing it in A2 of sheet1. I do not want to create a drop down each time there is a value change in drop down1. Instead i just want to create a list of values and then just associate this list with A2 of sheet1. Whenever there is a value change event, then i should be able to delete the previous contents of A2 of sheet2 and associate A2 with a fresh list of values.
2. Also if am able to associate a list with A2, i would also want to associate a value change listener with this list.

Is this possible? Please help me.

PS: As per my requirement, i have to create drop down1,associate value change listener with drop down1 and also create dropdown2/list dynamically using script.



I'm trying to create a dynamic drop down list however I'm completely stuck. Data | Validation, doesn't allow you to perform a function to search for the data to supply.

Here's an example.

Colour - Object is the list of data to be referenced on one sheet.

Red - Cat
Red - Apple
Blue - Sky
Blue - Eye
Blue - Fire

Other Sheet.

Cell 1 - 'Red' is selected
Cell 2 - the dynamic list filters the object range because "Red" was pre-selected in Cell 1 and returns the values Cat and then Apple in a drop down list here in Cell 2


I'm sorry but the plugin to create VB code of the spreadsheet isn't compatible with my excel due to company security lock down on software and add ins. So I can't show you the sheet layout. I hope my explanation about is clear.

Please help!

Thank you.

Hi, I'm trying to make a chart that depends on dynamic named ranges. I've got the dynamic ranges figured out (I think) -- but I'm having trouble getting the Names recognized. I can create them but they don't show up in the Names Drop-Down list. As a consequence, the names aren't recognized in chart source data references. Help!

Here's what happens.

1) I use the Insert > Name > Define tool to create my dynamic ranges. I think this part is OK. For example:


If I click inside the above formula, Excel selects the correct cells, so I think the formula is OK. The problem is the next step.

2) If I OK the Insert Name dialog and go back to my worksheet, none of my new ranges appear in the Names drop-down list. If I go back to my Insert Name dialog, all of my ranges are still there.

So I tried a different method of creating names. If I create a simple one directly in the worksheet (select cells in sheet, then click in Names box and type new Name) -- then the new Name does show up in the Names Drop-Down. So far so good. If I open the Insert> Name> Define dialog, then my new Name is there. Cool! So I tried to edit my new Name. I left the title alone and just changed the reference. All seemed well... but if I go back to my worksheet, the new Name is gone, no longer in the Drop-Down.

So- any tips as to why my names won't show up in the Names drop-down box? I've tried this in two different workbooks with the same problem and I'm stumped. Any help would be most appreciated!


I want to create dynamic drop down list. PFA the sample sheet.

In "Sheet1 B5" I have a drop down list with values, "Sheet2" contains the respective values for the selection made in 'B5"

e.g, If I am selecting "Test1" in B5, I should get a drop down list of "Val1, Val2, Val8" (which is already defined in "Sheet2") in "C5".

It would be great if I get a macro to do the same instead of formulas.

Please help...

I have a workbook with (3) tabs. Tab #1 contains a column with a large list of dynamic data. Tab #2 contains a drop down List made through "Data Validation". Tab #3 has the same number of rows as Tab#1 and looks at each row for a text value and returns a text value if it exists otherwise it returns a blank value. The List on Tab#2 references the list on Tab #1.

The problem I'm having is that when I make the drop down list on Tab#2, it shows all of the blanks for all the cells on Tab#3 that returned blank values. Is there a way to have the list only show values return a text value even though there is a formula in each cell that returns a blank value?

I've tried setting up a dynamic range but it doesn't seem to work.

Hi, I produce a report at the beginning of each month and there are 30 worksheets in the report. The report has a drop down box on each worksheet allowing the user to select the month of data they wish to see. The problem I have is that each month I have to extend the data validation range on each worksheet by one row (to show the additional month, i.e on Sheet1 which is a hidden sheet i have listed Jan, Feb, Mar, April, May, June, July etc on each row and each drop down box refers to this range) - it takes me a long time to do this for every worksheet and each drop down box. is there a quick way to do this?

I'm creating a check register. Each sheet has a half dozen columns, and each sheet will be a single month. Of the columns, 3 will have a drop down list that the user can pick a value from. It works fine if I put the values in a separate column on each sheet. But I woul like to have the values on a sheet by themselves, and then the list reference that sheet and column. When I try to enter the source into 'data validation', the only thing that appears is the formula, not the list.
For example, the drop down shows the formula... ='xxxx'!(a1:a20) instead of the list ie..
Vendor #1, Vendor #2, etc.

What am I doing wrong???

Thanks in advance.

Hi there!

I'm trying to utilize dynamic named ranges for a dependant drop down menu and I can't get it to work.

In a nutshell.......

The main sheet contains 3 drop down menus adjacent to each other, which the 1st is independant and the other 2 are depdandant on its predecessor. Various other cells throughout this sheet autopopulate with values based on the selections of the drop down menus.

The 1st drop down menu utilizes this range for data validation:
=OFFSET('Species List'!$A$5,0,0,COUNTA('Species List'!$A:$A),1)
This effectively retrieves the desired list, and this list adjusts according to new/removed entrys to the list. The list retrieved is a list of animal species, fyi.

The 2nd drop down menu utilizes this range for data validation:
This causes the drop down menu to refer to the value in cell B9 (the 1st drop down menu) and use that as a reference to return the next desired list, which is a list of samples based on the specified Species in the first drop down menu. Both the name of the named ranges (that I want dynamic) and the selections from the B9 drop down menu are identical in terms of characters used.

I have on another sheet, Species List, aside from the list used for the 1st drop down menu, an array of currently named ranges, where each column represents a species and below that header is a list of samples. SO, i'm trying to make these named ranges dynamic with this code:
=OFFSET('Species List'!$C$5,0,0,COUNTA('Species List'!$C:$C),1)
However, with this code utilized, I can't get the 2nd drop down menu to retrieve the dynamic lists.

I know I could just say screw the dynamics and make the named ranges to include space for new entries but I'd really like to keep things as simple and aesthetic as they can be.
I'll attach a copy of the excel file for those who would prefer a first hand look, fyi I have only attempted to adjust the named ranges for Moose and Elk to be dynamic, the rest I have left as normal named ranges as I can't get the dynamics to work.

Any thoughts? Help is mucho appreciated!


Reversing a Dynamic Drop-Down List in Data Validation..I always use the OFFSET function to get a Dynamic List of Items in the regular Drop-down box..But I have a requirement where I need to use the Last Entry First as these options would be the most recent ones..So i have to keep them for Selection on the top..Regular Dynamic Validation formula[ =OFFSET($B$2,0,0,COUNTA($B:$B)-1,1) ]Revering the List..[ =OFFSET($B$2,COUNTA($B:$B)-ROW(),0) ]The only problem is to always reserve a Helper Column to store the Reversed List and then the same can be used in Data Validation?I would be happy if someone could help me by-pass this helper column and help me with a Dynamic as well as Reverse List..!

Hello, I'm new to the forums and I hope this post isn't in the wrong section.

I have a dynamic list that needs to be validated in another sheet (same workbook) in three different ranges eg. (b3:b15) (j3:j15) and (p3:p15).

The dynamic list contains options that should not be used more than once.
I'm unable to figure out how to make the drop down list to remove any used items.

I would greatly appreciate any solution and thank you in advance!

I'm trying to find a way of having a data validation list (to us in a drop-down) that will be generated from a list of data on another sheet tab. The list of data can contain duplicate entries but I want the drop-down to show only the unique values. Also, the drop-down must be dynamic because changes can be made to the source list.

Col A

The drop-down list should give me only one instance each of Apple, Pear, Lemon and Orange to choose from. I'll use a defined name for the validation list so that I can use it on the sheet where I need it.

Can anyone help?


hi all ,

i need a sample macro to create the output based on two drop downs,

i.e consider an xample
first dropdown is leavetype -the dropdown contains two values planned and unplanned
second dropdown is month- the drop down contains month values...

based on the two dropdown inputs my output should get generated..i.e if he selected planned then month as may then the ouput should be calculated based on these two dropdown inputs...

please help



I have a rather simple problem (tough for me!). I am setting up a food log. I would like to have one column with common food items that I have in a drop down list. The list would be about 20+ things. The other columns would have relevant nutritional info such as calories, fat, carbs, etc.

The problem is how do I make the values in the other columns change depending on which food item I pick from the drop down list?

For example, if I choose bread from the list, how would I get the columns for carbs and calories to change accordingly.

The IF function would be too hard because I couldn't nest 20+ IF functions in the formula for each cell. Also, the drop down list would change as my diet changes, possibly reaching 50+ things.

Also, I would like to manually enter a serving size that would alter the nutritional values in each column (for example 0.5*Calories or Carbs).

It seems so simple in my head, but I can't figure out how to get it to work. Thanks for any help you may have.

Once again, thanks for all the help so far.

I've set up a dynamic name range using


which refers to a list of numbers 1 to 26 and includes a blank cell at the top. The Dynamic Name Range is then used in another work sheet to set up a Data>Validation drop box list.

The Drop box list does not display the last entry in the list.

If I add extra numbers to the bottom, eg. 27, 28 and so on. The Dynamic Name Range expands to include the whole list except for the last entry.

What have I done wrong?



Dear Experts,


I have a query regarding the "Drop down list".
I am trying to develop the dynamic drop down list.
The drop down list must be updated automatically when data rang increased.
I have attached herewith the "Book 6" as example for your kind reference.

How to develop it? using formula or VBA code? please guide.

Thanks in advance...

I have 2 sheets right now. (Main and Month) as an example.
On the 'Main Sheet', I have a list of names which I named 'Name'.
on 'Month Sheet' I created a 'drop down list' using the validation list option and that works fine for selecting the names.

My problem is that everytime I try to look for a name to select from the 'drop down' list, it goes right to the end of that list thereby showing me only an empty cell which makes me and others scroll back up to the top to view the populated list.

is there a way to make this drop down list show me the very first entry vice the end of that list (empty cell)?

Thanks in advanced!

I have a list of products (A,B,C,D,.....H) in sheet1. and products informatoin in sheetB (sales, inventory,profit etc). I create a drop down list of the products in Range("A4") of sheet3, and create some summary table for sales,inventory, profit....

Now I want to create a VBA to copy those tableS in sheet3 based on each prodcut to a Powerpoint file and and save to a powerpoint file by each product.

My question is how to change value of a drop down list in VBA to be equal to one of my list of product names in order to create a powerpoint file separately

For example

The drop down list have product values (A,B,C,D,E,F,G,H)
My targe list to create PPT files is A,C,E

How i can change the value of the drop down list to be equal to the targe list automatically, and calculate it to create 3 (A,C,E) ppt files

I am not sure of what is it called, but I would like to be able to start typing in a cell and it to start giving me options until there is only one.

Right now I have 9 dynamic lists with anywhere from 5-20 names per list and I have a report form with multiple data validated drop down lists each pulling from a separate dynamic list. This works ok, but as the user tabs to a cell with the drop down, they must use the mouse to initiate the drop down and then select the right data. Even if the user types the right data into the cell, it will not pass validation. Is there a way to start typing and it to keep throwing away things until only one option is available and then be able to use the tab button to enter that data into the cell and move to the next cell? Or as another option how hard would it be to activate the drop down when the cell is selected and then use the arrow keys to select the right one and tab to the next cell? I would want to be able to tab through the cell if no information was needed from that dynamic list.