[Year 12 IPM] Mini PD - Drop-down lists in Excel

[Mark Kelly]

Hi all.  Was playing around with an ITA U4O1 task and wanted to force 
users to enter data with some control over what values they chose (e.g. 
genders, states of Australia).

Hereunder, please find a mini-PD on how to use a drop-down list to 
enforce validation in Excel - useful for ITA U4O1.

1.  Type the allowable values (e.g. "Male" and "Female") into a row or 
column of the spreadsheet.  Putting it on a different sheet to your main 
data is a neat idea to keep it out of the way.

2.  Select the cells into which you just entered allowable values.

3.  In the name box (just under the font name box), enter a descriptive 
name for the allowable values (i.e. name the range), e.g. genders.  Make 
sure you hit ENTER to make the name stick.

4. Now, select the cells into which you want to force users to select 
from your list of allowable values.

5.  Go to menu Data > Validation.

6.  In the "Allow" box, set it to "List"

7.  In the "Source" box, type an equals sign and the name of the range 
you created earlier (e.g. =genders)

8.  That's it.  Now, when a user clicks one of the cells you selected in 
step 4, they have to choose an entry from your limited list.

You can remove the list restriction by repeating step 6 and changing its 
entry to "Any value".

Have fun!

-- 
Mark Kelly
Manager - Information Systems
McKinnon Secondary College
McKinnon Rd McKinnon 3204, Victoria, Australia
Direct line / Voicemail: 8520 9085
School Phone +613 8520 9000 << new number!
School Fax   +613 9578 9253

Webmaster - http://www.mckinnonsc.vic.edu.au
IT Lecture notes: http://vceit.com
Moderator: IPM Mailing List