When you want to have user data entered in an Excel spreadsheet, you naturally want to avoid entering errors. This can be done by applying data validation: you simply have the possible options appear in a drop-down menu in Excel.
Step 1
As an example, we take three columns with a different place name as column title (cells A1, B1 and C1) with the names of the employees below (from A2 to C6 for example). Now you want, for example, in cell H1 a drop-down menu with the possible place names to appear. Then select cell H1, open the menu Facts and choose at Tools for data the option Data Validation. Bee To allow select you List. Click on the arrow button at Source and select cells A1 to C1. Confirm with OK
Step 2
If you want you can in excel also apply conditional formatting to the drop-down menu. For example, you want the chosen option to turn red when the location from cell A1 is chosen. Then again select cell H1, go to Start and choose Conditional formatting. Choose New rule and select Only format cells with in the top panel.
Step 3
In the bottom panel, choose the option in the first drop-down menu Certain text. The second menu leaves you set to contains. Then click on the arrow button and select as range cell A1. Back in the formatting rule window, press the button layout and choose for example at Font style the Colour red and the drawing style Fat. Confirm with OK.
Step 4
Suppose, in cell H2 you want to create a second menu that only shows the names of the employees of the chosen location (in cell H1). Then you start for cell H2 in the same way as in step 1. Also here you choose List Bee To allow, but this time you tap Source in: =Indirect(H$1). Confirm with OK and press Yes if you get an error message. Now select all employees of the first column (for example A2 to A6) and in the top left field change the name A2 in the column title A1 (in our example Haarlem). Repeat for B3 to B6 and for C3 to C6.
.