Overview

This article describes how to create a drop down list populated with sorted values from A to Z. The sorted list is dynamic and it adds new values as you type them.

Here is a list containing some random values:

Create a dynamic named range "List"

1. Click "Formulas" tab
2. Click "Name Manager"
3. Click "New..."
4. Type a name. I named it "List". (See attached file at the end of this post)
5. Type
   =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A$1:$A$1001))

   in  "Refers to:" field.

6. Click "Close" button

Create a dynamic list sorted from A to Z

1. Select Sheet2
2. Select cell A1
3. Type
   =IF(COUNTA(List)>=ROWS($A$2:A2), INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(A1)), COUNTIF(List, "<"&List), 0)), "") + CTRL + SHIFT + ENTER
4. Copy cell A2 and paste it down as far as needed.

Create a dynamic named range "SortedValues"

1. Click "Formulas" tab
2. Click "Name Manager"
3. Click "New..."
4. Type a name. I named it "SortedValues". (See attached file at the end of this post)
5. Type
   =OFFSET(Sheet2!$A$1, 0, 0, COUNTA(Sheet2!$A$1:$A$1001))

   in  "Refers to:" field.

6. Click "Close" button

How to create a drop down list containing dynamic values

1. Click Data tab
2. Click Data validation button
3. Click "Data validation..."
4. Select List in the "Allow:" window. See picture below.
5. Type
   =SortedValues

   in the "Source:" window

6. Click OK!

Download example workbook

Download excel sample file for this tutorial.
Create a drop down list containing alphabetically sorted values.xls
(Excel 97-2003 Workbook *.xls)

Related articles:

Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
Create dependent drop down lists containing unique distinct values in excel

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

OFFSET(reference,rows,cols, [height],[width])
Returns a reference to a range that is a given number of rows and columns from a given reference

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

COUNTA(value1,[value2],)
Counts the number of cells in a range that are not empty

ROW(reference) Returns the rownumber of a reference

ROWS(array) returns the number of rows in a reference or an array

Related posts:

Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula

Create a unique distinct alphabetically sorted list, extracted from a column in excel

Extract a unique distinct list sorted alphabetically removing blanks from a range in excel

Create dependent drop down lists containing unique distinct values in multiple rows

Extract a list of alphabetically sorted duplicates from a column in excel