Creating drop down list using Master data in another sheet

In the previous post we saw how to create a drop down list manually which is rather static in nature and you need to work with Data Validation tool every time you want to make changes in the list. To avoid this we can create a drop down list from Master Data provided in another sheet of the file and changes to this drop down will be as simple as adding one more value in the master list.

Sample Problem: I want to assign some cities to my sales staff and want to chose city from a drop down list. As I will be making frequent additions to my drop down list I want it to be simple to update.

Solution:
STEP 1: Create a sheet named Cities and provide some city names as shown in the below figure. On the Sales Staff sheet select the Cell for which you want to create a drop down list of the Cities. In the Data tab in the Ribbon click on 'Data Validation' button. In the Pop-up window select 'List' option as shown in the below figure.





STEP 2: Click on the Source text box and then navigate back to the Cities sheet. Select the entire column containing city names by clicking on the column header. Click OK on the Pop-up.



STEP 3: Check the selected cell in STEP 1 has a drop down for the city names. Drag this and apply the drop down to other cells in the column.



STEP 4: Go to Cities sheet again and add one more city: Singapore

STEP 5: Check the drop down now in Sales Staff sheet. It shows Singapore in the drop down!