creating a drop down list

  • Thread starter Thread starter Brian Dsilva
  • Start date Start date
B

Brian Dsilva

I would like to create a list of drop downs for a single cell but im a novice
and don't know how to do that or where to look for help.Please help
 
Brian
The simplest way:
Create your list of drop down items on your worksheet eg in cells Z1 down to
Z10
Then in say cell A1 from the Toolbar Data > Validation - in the allow box
select List which will open anothe box Source: enter in the box =$Z$:$Z$10
and click OK
Click on the arrow to the right of A1 and your drop down list will display
your items for selection.
You can of course create your list of items anywhere on your worksheet
providing you refer to the range in the source box. Extend the range from row
10 to suit your list.
Hope this helps
 
Hey Brian,
i just learned how to do this - there may be a better or more sophisticated
way but this is what worked for me. I added a new worksheet to the
spreadsheet where I want to use the list and called it "work area". On the
work area worksheet I created my list like this (I needed the drop down to
contain these choices: New, Open, Closed):

A1 blank
A2 New
A3 Open
A4 Closed

Then I selected all four of those cells and entered a name for them in the
name box (top left box above column header A that usually displays the cell
address) - I called my list ValidStatusList.

Next I went to the worksheet and cell where I want to display the list.
Once the cell is selected, go to Data->Validation->Settings.

In the allow box, select "List".
Make sure "In cell dropdown box" is selected.
In the Source box, put =Name (where Name is the name you gave the list
earlier)
Click OK.

If you want to color code the cell based on the choice selected from the
dropdown list, select the cell with the list and use Format->Conditional
Formatting. For my list above, I wanted to draw attention to the items that
were identified as "New" so the condition I set was:
Condition 1 "Cell Value Is" "equal to" ="New"
and then I selected "Format..." and on the Patterns tab I set the color to
purple.

I put the list on a separate worksheet because I actually had several lists
to make and figured I could just hide the worksheet once I was done so that
the users do not see it. I am not sure how else you might store the lists.
 
You could also enter the list values directly in the "Source" dialog.

Comma de-limited as in blank,new,open,closed


Gord Dibben MS Excel MVP
 
Back
Top