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

Auto Sort Values In Drop Down List

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

i had a list based on dynamic formula.When ever i update(Add or Delete) New items to my List the drop down list should also be updated and sorted in Alphabetical order(Ascending :Words Starting with A - Z).
How do i remove the blanks in the drop down list whenever i delete the data in the Source Data.

how to accomplish this task.

View Answers     

Similar Excel Tutorials

Sort Data Alphabetically or Numerically in Excel 2007 and Later
This Excel tip shows you how to Sort Data Alphabetically and Numerically in Excel 2007. This is a great feature to ...
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 ...
Easily Compare Duplicate Values in Excel
Here, I'll show you a simple technique to quickly and easily compare large lists of duplicate values.  This allows ...
Sorting Data by Date, Text, or Number in Excel
Sorting Data can be done with a few quick clicks of the mouse. I have used the same data as the previous 2 tutorial ...

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
Filter Data to Show the Top 10 Items from a Data Set in Excel - AutoFilter
- This Excel macro filters data in Excel in order to display the top 10 items from the data set. The macro uses the autof
Sort Data that Doesn't Have Headers in Ascending Order in Excel
- Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum
Filter Data to Show the Top X Number of Items in Excel - AutoFilter
- This Excel macro filters a data set to display only the top X number of items in that data set in Excel. This means tha
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

Similar Topics

Is it possible to create a drop down list using Data Validation that sorts the values from an unsorted list? If so, how? If not, how could I accomplish having a sorted drop down list from an unsorted source?

As an example; I have ListA (unsorted) defined in WorksheetA. On WorksheetB, I define a drop down list with data validation. Problem: the drop down list is unsorted. How can I get a sorted drop down list.



I'm using the Data Validation list to create a drop-down menu. It's getting the list from elsewhere on the same page - a list of names. But I'd like it to be possible to delete a name separately whilst the drop-down menu just ignores the existence of that blank cell when using the drop-down. Also, I need to be able to add new names and have the list remain in alphabetical order.

Any ideas?

Thank you so much.


I am trying to figure out how to create a drop down list on one page of a workbook with the list of items for the drop down list located on a separate worksheet within the same workbook.

I have a workbook with one spreadsheet with a list of items on it (sheet1/ingredients) and the rest of the worksheets would have the drop down list of items from sheet1/ingredients.

I have been trying to use the validation method with no luck - I just can't seem to get the source path right.

If possible I would also like the drop down list of ingredients to be self updating - the ability to continue to add items to the list and show up in the drop down lists.

When the value in the drop down window is selected I would really like / need to have the data populated in the rows to the right of the drop down window.

While asking is there any sort of auto complete so that one can start typing in the box and the ingredients that start with those letters show up to select from? (Such as if type EVA or EVAP --- then all words starting with EVA or EVAP appear instead of the entire list?)



1) Data validation in cell A1 allows a selection from a crowded drop-down list of about 30 items.
Most of the items on the list are lengthy technical descriptions, but they share some common keywords.

2) In order to speed up the selection from the A1 list, I would like to create a sub-list in cell B1 using some shared keywords, and use the selection to shorten the main list in A1.

3) Confusing ? Let me try a simple example.
Suppose A1 for this example has a list of only 4 items (data source Y1:Y4) instead of 30 items:
ONE and TWO with THREE of FOUR and FIVE
FIVE after ONE

And suppose B1 has the data validation list (data source Z1:Z3):

4) Selecting ONE from the B1 list in the above example should limit the list in A1 to the top 3 items (each contains ONE).
Similarly, selecting FIVE from the B1 list, should limit the list in A1 to the 1st and 3rd items. And so on.

5) Nested IF for the A1 list would not only be clumsy at best, but also would fail if more than 7 conditions (i.e.; 7 key words) are in the B1 list. (Keep in mind the actual items in A1 list is about 30 in the real application).

Any suggestions ?? Thank you kindly.

Hi all,

I have a worksheet which contains a Summary table and a data table. In the data table, I have some cells which has a drop down menu (created with Data Validation). I would like the summary table to update the sum depending on which item is selected from the drop down list.

Please have a look at the attached file. The summary table has categories which are already listed in the drop down list. If a user selects, say "Fairs" from the drop down list, I want that change to reflect in the summary sheet. ie: Sum of Fairs being updated. If later the item in the list is changed to "Sales Tools" in the drop down list, then the Summary table will update "Sales Tools" instead and re-adjust the "Fairs" total.

In other words, I am looking for a dynamic update in the sum of the items of the Summary Table, depending on any changes made in the items being selected from the drop down list.

Any ideas on how to do this?


Good Day,

I hope you are all fine!

mmm... can import an example that shows the drop down lists... Anyhow, pretend smileys are drop-downs. There are three drop down lists in a row and three rows in total. In other words 9 smileys (limited to only use 4 - so [Drop Down List] replaces image. The data contained in the drop down lists are numerical.

Row 1 [Drop Down List] Blank [Drop Down List] 6 [Drop Down List] Blank

Row 2 [Drop Down List] 2 [Drop Down List] Blank [Drop Down List] Blank

Row 3 [Drop Down List] Blank [Drop Down List] Blank [Drop Down List] 8

TOTAL 6+2+8 = 16

A series of questions will lead to 3 numbers being chosen. One from each row. So in other words, in Row 1, 2 drop down lists will be blank (no numerical value) and 1 will show a numerical value. In Row 2, 2 drop down lists will be blank and 1 will show a numerical value. In Row 3, 2 drop down lists will be blank and 1 will show a numerical value.

I would like to sum the values that show, no matter in which drop down list it appears.

Could you please assist?

Thank you


i have to make a dependent data validation..

But this one is a little tricky one. I dont know whether if its possible or not.. May be you people can throw some lite on this

1 w na y/n/na y/n/na na

2 x y/n/na na na y/n/na

3 y y/n/na y/n/na na na

4 z na na y/n/na y/n/na

Column A is a master drop-down list based on which the values in columns B,c & D should be changed.

if w is selected in col A
col B for that row should have only na in the drop-down list
col C for that row should have Y, N and NA in the drop-down list
col D for that row should have Y, N and NA in the drop-down list
col E for that row should have only na in the drop-down list

if x is selected in col A, then
col B for that row should have Y, N and NA in the drop-down list
col C for that row should have only na in the drop-down list
col D for that row should have only na in the drop-down list
col E for that row should have Y, N and NA in the drop-down list

and so-on.

Please let me know if this is possible with excel. I have no clue on Dependent data validation.

Much Thanks,

I have data validation applied to a cell using a list that's on another worksheet in the same workbook. This was done with a dynamic named range and works fine.

Now I want to change things so that items only appear in the drop down list if the value of a another column in the worksheet with the list items is set to "Yes". Each of the possible list items has either "Yes" or "No" in the column next to the list item.

Is there a way to accomplish selection of the items to appear in the drop down validation list based on the value of a column next to each of the possible list items?


Mike Clemens

Hello, I'm a pretty new Excel user and I've got a complicated task.

I have several columns in my spreadsheet which I need to populate based on the selection from the drop-down list in the previous column.

For example, I have Appliance as my first drop-down list. The options include: Refrigerator, Dishwasher, Microwave, etc.

I want to populate the second drop-down list (Manufacturer) in the second column with only the values that apply to the selection in the first drop-down list (Appliance). Each Appliance will have a different list of Manufacturers, so I have to create several separate lists and display the proper list when the selection is made.

Moreover, I have to narrow that down even further in the third column (drop-down list Mod#). This list would also populate based on the selection of the second list (Manufacturer) as there could be several options for each selection.

Can someone please give me some guidance on how I might accomplish this? Note: I know nothing about VisualBasic, which I fear may be the answer.

Thank you in advance, P_S!

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.

Hello all,

I've got a lot of drop-down lists in my file. When you click on the little arrow, I'd like the options to show up starting from the first option in my list. Right now they are starting from the middle of the list (example: The drop-down in B1 refers to drop-down list defined as A1:A10. A1:A5 of the drop-down list has items filled in, and A6:A10 are left blank so I can fill in more options later; the arrow activating the drop-down list in B1 shows options starting at A6. I'd like it to start showing the options at A1).

Is there an option so that the drop down will start at the first cell included in the list?


Need a way to indirectly refer one data validation list to another data validation list such that when I select one list, the 2nd list is reduced to only those items relevant to that list. I've enclosed a spreadsheet that more clearly outlines what I'm needing. Would also like to figure out how to sort the lists in alphabetical order in the drop down for ease in selecting.

I have added new data to a large Excel pivot table and I am trying to refresh the pivot table so that it only includes the new data. Some of the customer names in one of the fields are new and others are no longer being used. When I refresh the pivot table, the old customer names remain in the list, and the names are out of alphabetical order at the bottom of the drop down menu for the field.

Thanks to searching these boards I identified a macro that allows me to remove the old customer names fromm the field drop down list in the new table, but I have not been able to find a similar macro that resorts the field list so that the new customer names are in ascending/alphabetical order like they normally appear in the drop down menu. Is anyone aware of a VBA macro to refresh the sort on the field drop down menu? I would be extremely grateful for any suggestions. I do not want to have to remove the field, because it would force me to reformat it in multiple tables and I need it to work quickly.

The macro that I used to correct the old names problem was:

Sub Clean Pivots()
for each pt in activesheet.pivottables
pt.pivotcache.missingitemslimit = xlmissingitemsnone
End Sub

Is there a similar piece of VBA that would allow the newly corrected names list to be resorted into ascending order (or a line that could be added with another pt.pivotcache. command?) Thank you very much in advance.

I will try and be as succint as possible with this question.

I am trying to generate a list based on the value of a cell. That list will then be used as the range for a drop-down list.

Example: Cell A1 returns a value of 15
A drop-down list displays the values 1 through 15.

Cell A1 changes to 20
Drop-down list displays the values 1 through 20

I assume I'll need a two-step macro to accomplish this but I can't figure out the logic to populate the drop-down.

Thank you for your assistance.

Hi All,

I have 2 drop down lists in SHEET2, one is a list of 40 x 3 letter codes in alphabetical order in cell A1 and B1 is a drop down list contain 5 words.

In cell C1 is my macro button, which when I click on it, I want it to search SHEET 1, A1:A350 for the corresponding 3 letter code (all 350 rows are in alphabetical order) and in that corresponding row of SHEET1 column Y put the word from the drop down list.

I'm guessing that I will be needing the Worksheet_Calculate function, but have no idea what to do next as I was also thinking of using vlookup as a VBA command, but it doesn't exist.



Hi All,

I am looking to make a drop down box with the names of "items". The sheet where the drop down list will be referenced has blanks between the items, which makes the drop down list pretty ugly.
I was wondering if there was a way to either eliminate the spaces from the drop down list, or a formula to organize the data into a list without spaces on a different tab.

I have attached a document to help with the explanation.
In the attached tab:
I am trying to get the blue cell on the second tab populated with a drop down where the user can select between different options. The options are from the "Item" column on the first tab. As you can see, there are gaps between the items where the sub-items fit in. Is there a way to get rid of those gaps in the drop down?



I'm working on a small budget file to list all purchases. I have a drop list that take it's sources from 2 other excel files so these files have been referenced in the local speadsheet with an COUNTA and a OFFSET fonction. This works alright to generate a standard droplist from these items but my problem is that I need to generate one droplist that takes source in one of the other external document depending on a given caracteristic.

To put it simple, I need a drop list to work on the following logic:
IF X=1, Then use drop list from source A
IF Y=1, Then use drop list from source B

now since only X or Y can equal 1 and only one at the time, I tryed using a formula of the type IF X=1, Then use drop list from source A, Else, use drop list from source B

I just can't seem to get it working....
My current formula is =IF($D$5=1;OFFSET($AE$60001;0;0;$AE$60000;1);OFFSET($AF$60001;0;0;$AF$60000;1))

is this thing even possible to do in a drop list or should I just forget about it?


I am desperately seeking a way to list data based on one linked option selected in a drop down pick list. So for example select an author from a drop down list and excel automatically displays in another list all the novels they have published. I want to see the drop down list selectionb and all the results, save the selected data. I also do notwant the inputter to see all the raw data just that relevant to their selection.



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

Hi there everyone, I normally enjoy being presented with Microsoft Office puzzles from co-workers and can normally find the answers online, but I'm finally stumped. How do you edit a drop down list in Excel? This is a spreadsheet created by a former employee.

The drop down list is very simple, it offers a short list of initials of staff members in charge of various tasks. On the header, if you click the drop down tool you can sort the list by the staff members.

They are trying to remove the initials of the one of the former employees from appearing in the drop down list.

I have already tried the Data/Validation route but it looks like the person who created the list did not use this method to create the drop down list. I also tried Insert/Name/Define and it does show the drop down list under "names in workbook", and appears to reference a source in "Refers to", but I still cannot figure out how to edit the darn list.

Any help or suggestions would be greatly appreciated. Thank you

I have a drop down list which uses =if() to pick students from a separate sheet and this drop down list has got blanks in it because the students are from separate years for example you have year 7 then 8 then 7 then 9 then 8 then 10 then 7.
So my drop down list for all year 7's is something like this
Is there a way i can get rid of these blanks ?
Because i need a drop down list that will only show students from one year not so many

In simple words need a formula to get rid blanks tried counta blanck but since i already got a formula there wont work

Help will be much appreciated

I have a drop down list that has multiple items within the list. Based on the item that a user selects in that list, data is populated in the cells that follow. On my PC, I can change the drop down list and the data populates. However, on others computers, they change the drop down list and the data does not display. Any thoughts as to why this is happening?

I have 2 drop down lists, the second is dependant of the first using data validation and the indirect formula. Trouble is if I delete the first choice/or change it, the second list doesn't update. How do you delete/amend info in second list based on new info from the first list. Thanks in advance.
Attached :- Simple Worksheet

Hi everyone

Here is my problem

I make a drop down list of three items in column A row 2

the column is call Gama (cell A1) and the items are 1, 2 and 3
every item has 3 options 1a, 1b and 1c, 2a, 2b and 2c and so...

1a, 1b and 1c would be a drop down list name "small"
2a, 2b and 2c would be a drop down list name "medium" and so...

what I want to do next is:

select 1 from the column Gama and when I make that in the cell B2 would appear the drop down list corresponding to 1 which be "small"(1a, 1b and 1c)

What I can do in order to condition the second drop down list depending in the first one

In MS Excel 2010 How do I display only the unique values from a column in a drop-down list box in another cell and have it update the drop down list whenever the source list changes?
For example, if column A contains "chocolate, vanilla, chocolate, strawberry, vanilla, chocolate, strawberry, pecan", I want to have a drop down box in another cell which contains only the values "chocolate, vanilla, strawberry, pecan". I also need this to update automatically, so if I add a new entry to the source list (raspberry) then as soon as it (or any other unique value) is added it would appear on the drop down list.
I created the "unique records only" list from the master list using the Filter menu "Advanced" and then "Copy to" option. I then created the "Drop Down List" using data validation restricting the values to the range with the unique values in it. But the unique values list does not update automatically unless I do the filter again, and then I have to re-assign the drop down list to read the unique list again.