You can create the list of items for your drop-down on the same sheet as the menu itself. If you create the item list on a different worksheet, you can add them to the drop-down by creating a named range that you can reference. After typing the list items on another sheet, follow these steps to name the range: Highlight the list items on your other sheet. Click the Insert tab and choose Define Name… Type a name and click OK. You can protect or hide the separate worksheet to prevent other users from making changes to the list.
The Data Validation window should automatically open to the Settings tab. If it doesn’t, click the tab to activate it.
If you created a list on the same sheet as your drop-down menu, click the toggle button on the right side of the “Source” box, then click and drag to select all of the cells containing your list items. If your list is on another sheet and you named the range, type =RANGENAME, where RANGENAME is the name you gave your range.
Make sure “In-cell dropdown” is selected, as this is what turns your list of cells into a drop-down menu. You can also check the box next to “Ignore blank” if you don’t want to include blank cells in your list.
Check the box next to “Show input message when cell is selected. " Enter a title and message for your information box. The title will appear in bold letters at the top of the message.
Check the box next to “Show error alert after invalid data is entered. " To display a warning or information while allowing the user to still select the invalid option, select either Warning or Information from the “Style” menu. If you don’t want to allow the user to select an invalid option, select Stop from the “Style” menu instead. Type a title and error message for your error box into the fields.
