How to implement Drop-down list selection entry in Excel

When we use Excel to enter tabular data, we often encounter a column of data values only in a few fixed values of the case, such as: The Human sex column can only be entered into the male or female, the academic record may only be entered into high school, college, undergraduate, one of the graduate students. If we enter this type of data manually, the efficiency is low and error prone, the best solution is to provide a drop-down list box for us to select the values. Here is an example of how to do this by arranging a teacher's timetable, which allows you to select a teacher's name when filling out a form, and to select the name of the course in a different list.

Create a Data source table

In the Sheet2 table, enter the name of the teacher and the course in charge, and place the teacher's name on line 2nd. Select B2:f2, which is the teacher's name. Then enter a name for it in the name box "name" (Figure 1), make sure to press ENTER when you are finished and go to the Sheet1 worksheet.

Two data associations

In order to refer to the name in the Sheet1 table, select the (B3:B9) cell in the Teacher Name column Drop-down box, click "Data → effectivity" in the menu bar, select the "Settings" tab in the Pop-up Data Validation dialog box, select "Sequence" in the "Allow" selection box, and enter "in the source input box" = Name (Figure 2), when you click OK, you can select each teacher in the Drop-down list.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/Office/excel/

Tip: You can now realize the magical name box, because the source of the pick button is not across the table to pick up data from other tables.

The second step is to achieve the automatic selection of teachers responsible for the curriculum, because the teacher's name is changed, the name of the subject of responsibility will also change. The validity data in this column of the course is derived from the list of teacher names, how to solve this problem? Again, we can use the name box to resolve it.

Back to the Sheet2 table, you don't have to name each teacher in the table, it's troublesome and time-consuming. Select the entire area (B2:F6), name the first row of each column, click "Insert → name → specify", select only "First Row" (Figure 3) in the specified Name dialog box, and click "OK" to use in the Sheet1 table.

Go to the Sheet1 table, select the area under the Responsible Course list (C3:C9), and click on "data → validity → sequence". Then pay attention to the contents of the Source input box, because you can't be equal to a cell, here you want to reference the data in the name of the teacher's name to the Drop-down list, where you use the function indirect, which represents taking data from a cell and then converting that data into an area. In the Source input box enter "=indirect (", click B3 cell, appear "=indirect ($B $)", here is an absolute reference, press the F4 key to change to the relative reference "=indirect (B3)", determined that there will be a warning box, the source currently contains errors, Do you want to continue (Figure 4)? Click "Yes" to continue on the line.

Hint: Someone will be afraid to continue because of the "wrong hint". Why is there an error? This is because the B3 cell does not have a name in it, so the error prompt appears.

Now, click on the Sheet1 table in the B3 to the C9 area any cell will appear drop-down list box for you to choose the value you want to enter, if the teacher changes in the future or he is responsible for the curriculum changes, as long as the Sheet2 table to do a little change, easy to save!