Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

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 Named Range in Excel
How to create a named range that expands automatically when a new value is added to the range. This is a great lit ...
OFFSET Function in Excel
The OFFSET function in Excel returns a cell or range reference that is a specified number of rows and columns away ...

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

Background information: I am using Excel 2003 on Windows XP.

I am trying to get a unique list to create a drop down list.

I have a sales report where Col A=Product Name, Col B=Month, Col C=Year, Col D=Total Sale Value.
I am using "Conditional Sum" to get the Total Sale amount from Col D for selected Product for specific month and year.

I have created a drop down list for Month & Year. What I am looking for a drop down box which shows all the Products meeting criteria (Month & Yr) which are unique and sorted meeting the above two criterias.
So, I want to create a dynamic drop down list showing only those Product names which has undergone sale in that month & year.

I have already used Advanced filter but that's not dynamic.

Any help would be appreciated.


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!


Is it possible to create a DROP DOWN list in a CELL that picks up values stored in a different worksheet.

In short, i want to create a DYNAMIC pop-list or drop down list.


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,


I have a continuing problem.
I have set up dynamic list containing a number of values, in another sheet I have a lot of cells with data validation, which have been populated by the aforementioned list. However now I want to change a value in the list, e.g. x to xx, this however does not change the values in the drop-down cells. Is there a way, code or othervise, to update the drop-down cells based on the change in the list.

Any help would be appreciated.

I need to be able to create a dynamic dropdown list that will have its source list changed and thus have different length.

BUT!! Excel puts empty fields into the drop down list how do I prevent that?

Because sometimes the input list will have 4 values, sometime 20, but I can't make the input range smaller because if I then have 20 values only 4 will show. Help?

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 have created a spreadsheet that has worksheets which need to filled in for
every day of the month. There is a spreadsheet for each month of the year.
On each worksheet for each day there is a validation drop down list which
takes its information from a list on the bottom of each page. There are two
columns beside the drop down list that use drop down for a vlookup. The
problem i have is that the information will change from time to time and the
list for the drop down and vlookup will be updated. When the lists need to
be updated I have to go to each individual sheet from when it needs to be
updated, unlock the sheet, insert the new items into the list or update the
current lists information, resort the list and then reprotect the worksheet.
Is there any easier way of doing this?


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.


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 need to create two drop down lists where the 2nd one is dynamic. The list will have to depend on the value chosen for 1st list.
I have 7 different groups and each of them have different names.

Appreciate for any assistance.

Thank you.

Hong Chen


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 there,

I would like to create a dynamic drop down using data validation list.

I have a bunch of countries and regions listed. So I have North America, South America and Europe regions and then a bunch of countries in each region.

What i want to do is to have 2 drop downs. The first drop down is for a region so people can select either Europe, North America or South America. The second drop down would be for a country. My dilemma is how to make second drop down dynamic based on a first drop down. Basically I want only European countries to be displayed in a second drop down if soemone select Europe in the first. If someone selects South America then I want only SA countries displayed as selection options in the second drop down.

Currently i have a big list of regions (column A), countries (column B) and other metrics in other columns. I just need help with creating dynamic list (for countries) that is based on the selection in the first list (regions).

Thanks so much!!

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 - not sure if lookup is the right function here....and I'll do my best to explain.

I have used the Data Validation function to reference a list of values in another area of my spreadsheet. The result is a drop down list in column A that allows me to pick from the values listed further down in colmun A.

In columns B and C alongside drop down list I want to insert different cost values. The cost values I want to use are further down in columns B an C and correspond to the item I choose from my drop down list.

I pick "Large Bucket" from my drop down list. Large Bucket further down in column A has price values in columns B and C (same row). I want the price values to drop into columns B and C on the same row as 'Large Bucket" where I selected it from the drop down list....

Am I making any sense !!?

Thanks for any help

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