the "pick from list" selection for a cell

Posted by Tony on December 13, 2001 6:57 AM

When entering information for a new record, for most cells I want to choose from a list of data to be entered. I've found that right-clicking and selecting "Pick from list" provides exactly what I need.

However, there are more people than just myself entering records into this sheet, so I want to guard from error. Is there a way to make the default setting for entering data into a cell the "Pick from list" setting?

One thing I have tried is the use of Combo Boxes. They get me halfway there, however, I don't know how to get the combo box to repeat with each new record I want to enter, and I also do not know how to track combo box selections.

Thanks.

Tony

Thanks!

Posted by Joe Was on December 13, 2001 7:57 AM

Create a dropdown list that is populated with a named range of data.

On a new sheet or off to the right of your active sheet, build an un-labled list of values you want or copy the data(record)rows from your existing data to make your list.

Then click the column ID to highlight the list column. Insert-Name-Define then give your list a name.

Click the first data cell that you want the list in, then: Data-Validation-Settings (select "List" from Allow) in "Source" add =your list name, Like: =myList

Then copy the cell you just put the dropdown list in, highlight the other cells you want that dropdown in and hit special paste-validation.

Now when the active cell is entered the cell grows a dropdown arrow which when clicked has your selection list. JSW

Posted by Joe Was on December 13, 2001 7:57 AM

Create a dropdown list that is populated with a named range of data.

On a new sheet or off to the right of your active sheet, build an un-labled list of values you want or copy the data(record)rows from your existing data to make your list.

Then click the column ID to highlight the list column. Insert-Name-Define then give your list a name.

Click the first data cell that you want the list in, then: Data-Validation-Settings (select "List" from Allow) in "Source" add =your list name, Like: =myList

Then copy the cell you just put the dropdown list in, highlight the other cells you want that dropdown in and hit special paste-validation.

Now when the active cell is entered the cell grows a dropdown arrow which when clicked has your selection list. JSW

Posted by Tony on December 13, 2001 12:51 PM

Many thanks -- and additional question

Joe,

Thanks so much for this solution. My co-workers are appreciative as well! This list is exactly what we wanted, and we are saved a click of choosing "Pick from list".

One more thing, however. Even with this list, it is possible to type in a value that is not included in the list. Is it possible to prevent this?

Either way, our tracking just got easier. Thanks again. - Tony

Posted by Joe Was on December 13, 2001 3:26 PM

Restrict selection to list only!

Data-Validation-Settings
Allow: List
Check: Ignore blank
Check: In-Cell dropdown
Source: =$AA:$AA
Note the =$AA:$AA is the list data column, in this case it was and is named "myList" the column it is in is "AA" Just change =myList to =$AA:$AA

Then

Tab to Error Alert
Check: Show
Style: Stop
Title: Error!
Error Message: Only select from the dropdown list!

Now the user can put a wrong entery in the cell but they cannot move from the cell without an Error Box Message and option to fix it or erase it and start over! JSW

Posted by Tony on December 14, 2001 9:02 AM

Thanks again!

Works like a charm!