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

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

Highlight Duplicate Values in Excel
How to highlight duplicate values in a list. Also, how to arrange those values next to each other so they are easy ...
Sort Data Left to Right in Excel
How to sort columns of data in Excel.  This is the same as sorting left to right. This will change the position of ...
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 ...
Sort on Multiple Columns at Once in Excel
How to sort a data set by multiple columns in Excel.  This allows you to better organize a data set and is really e ...

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.

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.

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

I want to create seven text boxes, with the first one having a drop down list and the other six boxes automatically filling in based on the selection in the first box.

I plan to have 7 lists, the first of which will be the selectable items in the drop down list mentioned above. Each of the next six lists will hold the values that need to auto-fill the remaining six boxes. Each set of values will be on the same row as the item selected in the drop down list.

I just can't seem to get to the point where excel understands I want the selected item in the first box (the drop down box), then the next box has the value in the cell adjacent to first list, the next box will have the value in the 2nd cell adjacent to the first list...etc (example: I select, from the drop down list, the value in A16...then Box1 = value in A16, Box2 = Value in B16, Box3 = Value in C16, Box4 = Value in D16, Box5 = Value in E16, Box6 = Value in F16, and Box7 = Value in G16).

Any help is appreciated.


Hi everyone,

(Windows 7, Excel 2010)

I'm using the INDIRECT and MATCH formula functions to create a data validation drop-down list that is based on a variable-length list of data items. When I setup the data validation list, everything is fine. But when I save, close, and re-open the spreadsheet, the drop-down list is empty (i.e. nothing happens when I click on the arrow) until I go back into the Data Validation dialog box and click OK (without making any changes).

I've put a spreadsheet that demonstrates the problem at [9k]

Here is a description of how I can reproduce the problem:

In a blank worksheet, enter some data values in column C, starting in C1 Select cell A1, and go into the Data Validation dialog box. Choose "List", and in the Source field enter: =INDIRECT("$C$1:$C$" & MATCH(2, 1/(C:C""),1)) Click OK You should now have a drop-down list in cell A1 with the choices being the contents of column C. If you add or remove a value from column C, the choices in A1 should update automatically. Save the spreadsheet, close it, and re-open it. Click on A1 and try to open the drop-down list. Nothing should happen when you click on the arrow. This is the problem I'm seeing. If you then select A1, go back into the Data Validation dialog box, you'll see that the formula is still there properly. Click OK without making any changes, and now the drop-down works in A1 again. Any suggestions or pointers to information about this problem would be very much appreciated!


Hello, I would like to ask you if you can help me.
So I would like to do:
1. on the 1st sheet I want to have some columns and that you can choose content from drop list
2. the same table I will put on the 2nd sheet and use is as database, each column fills drop list from 1st sheet
3. I want that my drop list is automatically updated if I change content from the column in 2nd sheet (base for data)

I would appreciate your help with example, I'm a beginner in Excel 2007.

Thank you !!


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.