Auto Sort Values In Drop Down List

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.

Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

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

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

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?



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.



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

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.

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 have searched all over the internet without any answers for this question.

I have a two tab excel spreadsheet with a drop down list. However, for this task I cannot modify anything on the Data tab but need to create a drop down list of non-duplicated names in the List Tab. How in the world are we able to do this? The best case scenario is that I do not need to modify data in any other cell and can do this via formula in the List Box data validation. I am open to any ideas though! Please help!

I appreciate anyone that can help and I added an attachment.

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

I'm trying to figure out how to auto fill values of different cells based on which value I choose from a drop down list in Excel 2007.

I have the drop down list created, and the only way I could figure out to auto fill the values was to do =IF(A2=M2, "Value A", IF(A2=M3, "Value B", IF(A2=M4, "And so on...")))

Several things with this though, if I need to add additional items to the list, I'd have to go through each IF statement and add them, AND, will have multiple columns and rows that I will be tallying.

What's the "easy" way to do this?

I have an issue with attempting to remove blanks that appear within the drop down list.

The list is in a seperate tab within the same workbook (i understand you cant reference seperate workbooks) this however means that "remove Blanks" no longer works.

The good news is that all the blanks will be at the end of (bottom) of the column im referencing. This sheet is referencing a seperate workbook to overcome the issue of referencing seperate workbooks within a Validation list. As this seperate workbook sheet will continue to grow i am referencing cells that have not yet been filled.

I have used the following formula in order to change all the zeros i recieved in to blanks.

=IF(ISBLANK('\\Server\[live job list.xlsx]Live Job List.rpt'!A2135),"",'\\Server\[live job list.xlsx]Live Job List.rpt'!A2135)

I need something that will stop blanks from appearing within the Validation Drop Down Box.


I am trying to populate a drop down list in Excel based on the solution
proposed on the website

However, I find that the list only gets updated if I do a Ctrl S (save the

Have any of you faced a similar problem before with excel drop down list?

I have created a drop down list in an Excel file. I set it up for a cell to
refer to to the list via Data - Validation, but when the cell is chosen, the
drop down arrow does not appear. It has worked for months but it has now
stopped. I tried to delete it and re-create it, but it still does not show
the drop down arrow and list. What do I do?

Hi there,
Is it possible for a drop-down list to have quick select or speed select functionality?
For an example, considering the following alphabetized drop-down list "apple, banana, cantaloup, corn, kiwi". If I would like to select "corn", is there a way set-up the drop-down list such that to select "corn" only the first letter of the word "c" needs to be typed, which would bring me to the start of the "c" items in the list to then select "corn". It seems like scrolling the drop down list using the mouse is the only way to choose items.

Any feedback would be appreciated.


I'm trying to build a drop down list using the values in a range of cells on
the same sheet. I Select about 150 cells and then using the Add method of
the Validation object to create the validation on this range of cells. The
code runs and all 150 cells now have a drop down list. The problem is that
the drop down list for the top cell in the range has a complete list of
values. As you go down the list of cells and click the drop down list, the
list of values gets shorter and shorter until finally it's blank when you get
about half way down the list of 150 cells.

The point at which the drop down list comes up blank seems to correlate to
the number of cells in the range used as the Formula1 in .Add method.

anyone seen this before or know what's going on? Love some suggestions.



After running a macro, I need to disable a combo-box (drop down list). My questions a
How do I identify the drop down list (name of the drop down list). There are 3 drop down lists on the sheet and about 26 of them in the whole workbook. What is the VB Code for disabling/enabling the drop down list, once it has been identified. Thanks for the help.

I have a lot of data that I built macros to sort when you when to see certain categories.

I had it so if you wanted to see Portfolio A, you click a button and it sorts the list of 100 names into the 25 that are in Portfolio A, and etc for other Portfolios.

However, now I have too many buttons and I would rather be able to use a Drop Down List.

I want a drop down list with 10 names on it, where for each name that I choose from the list, it runs a different macro. Is this possible?

All I can figure it is how to assign 1 macro to the drop down list, which seems useless to me. How can I make each name on the list assigned to a macro?

I was wondering if it is possible to have a drop down list where you can type in the first few letters of a word and have the drop down list go to the the words that begin with those first few letters instead of having to scroll down the entire list to find the word / item you are looking for.
I ask since the drop down list may end up having up to 1000 words in it.


I'm creating a database for files at work. In each "job folder" there are sub folders. I have a column that is titled "available file sets" which I want to have a drop-down list that shows ONLY the files sets that are actually available. I understand completely how to make a drop-down list using specific cell information, but in my case, the information changes from job to job and i do not want to create 20 different lists in order to use Data Validation. Is there a way to add a drop-down list where i can change the items for each job i have without selecting information with other cells.


Say in Column "A", i have my drop down list. It will extend all the way down the sheet. The Items, or "file sets" in the drop down are the following: Architect, contractor, general, legal, invoices, requisitions, progress schedule, meeting minutes, testing, change orders, and subcontractors

Each Row is a DIFFERENT job(610 to be exact), and may not have all those items in the file set. So i need a way to pick and choose which ones i want in the drop-down list for each job without having to create 20 lists so when i use Data Validation, i'm not limited to selecting a range of cells.

Does this help?


I need to create a 'sub' list from a drop down list.
I have 8 categories in a drop down list in column A, but i need column B to also have a drop down list of sub categories depending on what category is selected in column A.

And once column B is populated, i need column C to auto populate with a value. All data is stored on the spreadsheet.

I have ten items in a list in column A. I want a user to be able to give each of these items a score or ranking 1 through 10, so two items can't have the same score. There will be drop down lists in column b. In cell B1, next to the first item, there will be a drop down list with 10 choices 1 through 10. In cell b2, next to the second item, there will be a drop down list but if the number 6 were selected in the first drop down list the user would only be allowed to pick 1,2,3,4,5,7,8,9,10. And so on until the last selection would be whichever number was left over. Make sense? Any ideas on how to accomplish this?


I have two questions: 1) How do I create a default value in a Data Validation list? I have a number of carrier names in my list. However, 1 carrier is used 50% of the time so I would like that to be the default name unless the user selects a different carrier. 2) How do I make the font in a drop down list larger. It's hard to see some of the values in the drop down list when selecting from the list. Thank you!

I have two questions: 1) How do I create a default value in a Data Validation list? I have a number of carrier names in my list. However, 1 carrier is used 50% of the time so I would like that to be the default name unless the user selects a different carrier. 2) How do I make the font in a drop down list larger. It's hard to see some of the values in the drop down list when selecting from the list. Thank you!

I have a drop down list which consists of a huge amount of column. However there are several duplicate entries in the column and it causes my drop down list which refers to that column having duplicate entries also.

How am i able to remove the duplicate so my drop down list will look clean and neat?

When using the data validation for a drop down list, I can't remove the blank entries. Is there any solution to this? I searched a few methods but none worked. There are two scenarios:
1. three cells as the source:
non-blank blank blank
2. and one combination cell and one non-blank cell.

please see my attachment for details. I'm using Excel 2007.


Is it possible to use data validation on a cell that as you begin to type what you want, a list of all possible matches from a list appears in a drop down list to choose from?



As you start to type the 'C', the entire list above would appear. When you then type 'O', Cummings would drop off the list etc etc.

I may be asking for the impossible, I really don't know.

Thanks in advance.


I'd like to have other cells in the row of the drop down list change according to what I've chosen in the list. Is a drop down list the best method for this application?

I have a table of material properties that I would like to read from based on what material is chosen from the drop down list.