Create a drop-down list
※ Download: Create drop down list in excel
Repeat steps 5 and 6 above to rename your tables. To do this, instead of typing values into the Source field, you want to select the icon to the right.
Next, you must create a separate table for each of the hospital locations. In this case, we enter A12; 3.
How to Make a Yes or No Column in Excel - But how do I provide a drop down list that provides the option to complete a response e. To delete a drop-down list, see.
Drop-down lists in Microsoft Excel and Word and Access allow you to create a list of valid choices that you or others can select for a given field. Creating dependent drop-down lists when combined with an INDIRECT function is another benefit. This allows you to select a product category from the main menu drop-down list box such as Beverages , then display all the related products from the submenu dependent drop-down list box such as Apple Juice, Coffee, etc. This works very well for ordering and inventory purposes because it divides all the products into manageable categories. This is how most wholesale and retail companies handle their product lines. Practice Excel drop-down lists using the data in this workbook. If your spreadsheet database is large or contains numerous fields, we recommend that you place the list box items in a table on a separate spreadsheet, but in the same workbook. However, if your list is relatively short, you can type the items for your list, separated by commas, in the Source field of the Data Validation dialog window. Enter the names of 10 doctors or other applicable items in column A from A1 through A10. Sort the list to your preference. If you plan to sort by last name, enter the last name first, then the first name and middle initial on your original list. Highlight the range A1:A10 or just position your cursor on any cell in the list, and press Ctrl+ T to convert this group of items to a table. Excel calls it Table 1, 2, 3, etc. Move to Spreadsheet 1 renamed wks. Enter some data similar to that shown in the following graphic, for example: Type of Surgery, Date, Time, and Surgeon, or create your own data. Select the cell or group of cells where you want the drop-down list to appear. In this case, select D2 or D2:D11, if you prefer, though it's not necessary to highlight the entire column. In the Data Validation dialog window, choose the Settings tab. In the Validation Criteria panel in the Allow field, select the option called List from the drop-down list box. Tab down to the Source field and click inside this box. Move your cursor outside of this dialog window and select the lists spreadsheet from the workbook tabs at the bottom of the screen. Highlight the range of doctors—that is, A2 through A11. Next, click the Input Message tab and enter a Title and Input Message for your drop-down list. Next, click the Error Alert tab and enter the Title and Error Message for your drop-down list. Click OK and your drop-down list box is complete. Move back to the wks spreadsheet and position your cursor in cell D2. Notice the arrow for the drop-down list box, and your custom Input Message appears to the right of each cell in this column that you select. Click the down arrow and choose a doctor from the list that specializes in the type of surgery on the corresponding row of column A. Click Cancel to exit this dialog. Create dependent drop-down lists Dependent drop-down lists are like the submenus in Office applications. The main menu or drop-down list displays various options with submenus below each one that display further options related to the main menu. In our sample worksheet, the drop-down list provides a selection of surgeons for you to choose that matches the type of surgery scheduled. For this next exercise, imagine that you run a small rural hospital located about 50 miles from a large city that has three large, completely staffed hospitals. The submenu drop-down lists provide the names of each surgeon that works in each of these facilities: East Side, West Side, or Midtown. Create the lists 1. First, add another spreadsheet and name it lists2. On the lists2 spreadsheet, enter the following title for column A: Hospital Locations. Under Hospital Locations enter the names EastSide, WestSide, and Midtown in cells A2, A3, and A4, respectively without spaces or use one word. Move your cursor to the first cell under the title Hospital Locations A2. Select the hospital locations in this list A2:A4. Enter a table name Locations in the Name box above column A or press Ctrl+ T to convert these items to a table, which Excel names Table 1, 2, 3, etc. Finally, check the box that says My Table Has Headers. Cursor down to Table 1 2, 3, 4, etc. In the Edit Name dialog box, type in the new name Locations. Note: Excel does not allow spaces or other special characters. Next, you must create a separate table for each of the hospital locations. On the lists2 spreadsheet, enter the following titles, for column B: East Side, C: West Side, and D: Midtown these column labels will also be your range names minus the spaces. Enter some doctors' names under each of these three columns B, C, D. Format each list as a named table repeat step 3 above. Highlight the range of each column individually B1:B8; C1:C7; D1:D9. Press Ctrl+ T to convert these groups of items to Tables, which Excel names Table 2, 3, 4, etc. Repeat steps 5 and 6 above to rename your tables. Remember, no spaces in range names. Note: If you have multiple tables, name them based on the header you provided for each column. Create the drop-downs 1. First, return to the wks spreadsheet and delete the previous drop-down list in column D titled Surgeons. Create a new header in column D1 titled Location, and name column E1 Surgeons. Next, select cells D2:D11 for the main menu drop-down list. In the Data Validation dialog window, choose the Settings tab. In the Validation Criteria panel in the Allow field, select the option called List from the drop-down list box. In the Source box, click the list2 spreadsheet, highlight the Hospital Location list minus the header A2:A4 , and click OK. Move your cursor to cell E2. Repeat steps 8 and 9 above. Note: If you receive the Source Error message, just click Yes, because the errors will cease when the data from the drop-down lists fill in. This activates the entire range. Click the drop-down arrows one at a time in column D Location. Choose a hospital from the list, and it appears in the active cell. Move your cursor to column E Surgeon and choose a doctor from the list of doctors at the location you specified in column D. Work-around for two word items If you want to use two or more words for the main menu drop-down e. Translation: substitute cell D2 that has a space with D2 minus the space. If you need additional help, you can download this spreadsheet here:.
For more information about how to protect a worksheet, see. Microsoft Excel has rudimentary database capabilities, and you may already be using spreadsheets in your day-to-day operations. How to create a searchable drop down list in Excel. Then drag the Fill Handle in E2 down to E9 to fill the cells. Tab down to the Source field and click inside this box. If a user types in the cell rather than using the list, Excel checks the contents against the list and displays an error message if the contents aren't allowed. To delete a drop-down list, see. If you use the OFFSET function, as shown above, even if you add more items to the list, the drop down would not update automatically. To do this, instead of typing values into the Source field, you want to select the icon to the right.
