Other Links

This webpage is an extract of the downloadable
Tutorial on Excel

The drop-down lists in Excel

Lesson 2-2: Excel Drop-Down Lists and Data Validation

Part 2:

Cells addresses as source for Excel drop down list

In the "Source" text box submit a range of cells where you maintain your list. ex(=$G$1:$G$23). Don't forget the equal sign (=) and the dollar signs($). If you don't enter the dollar signs and you copy you DDList it will offer values in range G1:G23 in the first one then G2:G24 in the cell below and G3:G25 in the next...

Your list can be dynamic. For example if you list comprises 5 names you would submit range G1 to G6 and if you want to add a sixth name you will need to go back to your DDList and change the range. To avoid this irritant set the range to G1:G25 even if you have only 5 names. From then on you can add 20 names to your list and not have to change the DDLists.

Another irritant appears when you submit a range with many empty cells. When you click on the arrow of the DDList the first empty cell is selected and all the names might not be visible within the DDList  unless you use the scroll bar . To avoid this problem submit range G1:G25 but start entering names in G2. The problem is solved because when you click on the small arrow cell G1 is selected within the list and all the names are visible (unless you have more than 8 in which case you need to use the scroll bar.

In Part 1 you will discovered how easy it is to create a drop down list in Excel
In Part 2 you will discover how to use a range of cells addresses as source for Excel drop down list
In Part 3 you will discover how to assemble all your lists on a single sheet to maintain them easily and how you can refer to a list on another sheet to feed a drop down list. (in the downloadable tutorial)
In part 4 you will discover how to create cascading drop down list where the values offer in the second drop down list depends on what has been selected in the first one. (in the downloadable tutorial)
In Part 5 you will discover how to modify, extend and delete your drop down lists
In Part 6 you will discover different ways to automate your reports with drop down lists in Excel

This webpage is an extract of the downloadable

Tutorial on Excel