Business Solutions: Excel-Give Users a List to Choose From
By James D. Duncan, CPA  June 2006


         Every chance to help the user with input should be taken. This month let’s talk about how to help the user with input when the choices are known. This could be months, colors, inventory numbers or such things as sizes. Excel has a method to let the user choose from a drop down list. This eliminates spelling errors and usually is much faster that manual input. This standardizes input for that field input also.
         For example open a sheet and enter “Small”, “Medium”, “Large” and “Extra Large” starting in E1 down to E4. Enter “Size:” in cell A2.



Click on the “E” column header to select that column.
 

        This is the basis for the drop down list. Now click on Insert, Name and define. Enter “Size” in the Names in workbook section. Notice that the Refers to has column E already defined. Now click on Add button. And then click on OK.



         Move to cell B2 and select Data and then Validation. The following screen appears.

        On the tab Settings under Allow click on List.



        Be sure the Ignore blank and In-cell dropdown are checked. Now in Source enter “=Size”. Click on OK button and you have a list to choose from in B2.
        Now when we click on the selector button the list is displayed for us to choose from.
 

        Now, get busy and help your users get the right answers in record time. There are a lot of uses for this little trick.
        Remember, there is nothing like helping your community. There is usually no reward other than that great personal feeling you get. That is plenty. Remember in the “good ole” movies the hero always just rode off into the sunset.

 

James is a member of the CCOKC and an expert in Access and Excel. Click here to reach James