Frequently, I need to produce Excel Worksheets that have drop-down lists inside cells. If you ever need to do the same and can’t remember how – this is for you. The screenshots are taken from Excel 2007 but I have included references for Excel 2003 as well.
Personally, I always prefer to put the data that builds my drop-down lists on a separate worksheet. So create a new worksheet and rename it to Lists or something and open that worksheet. Now, lets say for argument you wanted three drop-down lists as follows:
Services: WAN; Email; Desktop; LAN; Applications
Categories: New User; Delete User; Change of Permissions; SAP; Exchange; Outlook
Severities: Low; Medium; High
On your Lists worksheet put these lists into three columns and give them headings as shown below:
Lists Worksheet
Now select Insert > Name > Define (if you are using Excel 2003) or Formulas > Define Name (if you are using Excel 2007). The Define Name window will appear as shown below:
Define Named Range
Select the cells that this range refers to (in this case =Lists!$A$2:$A$6) and click OK. Repeat the exercise creating a named range for Categories and Severities.
Now click back to you Main worksheet which for sake of arguement looks like the worksheet shown below:
Main Worksheet
Select the cell you want the first drop-down list to appear in (in this case C2). Select Data > Data Validation (in Excel 2007) or Data > Validation (in Excel 2003) and the Data Validation window will appear as shown below:
Data Validation of Cell
Select List from the Allow drop-down list and in the Source text box enter “=Services” as shown above. Repeat the exercise for the other cells on the Main worksheet (in my case D2 “=Categories” and F2 “=Severities”) and your good to go. All you now need to do is copy row 2 to the lower rows.
Hope this helps. As always, feedback is always welcome (both positive and negative).
JOATIT 