Implement fast entry in Excel 2007 with sequence

The school prepares to count some of the basic information of some students, in order to realize the quick entry and avoid the format confusion in the previous statistics, in the Excel 2007 worksheet The section of the Middle school student and the class are ready to use the form of the Drop-down list to enter. The school is divided into four departments, each department has more than 10 classes. If you put all this information in a list, it's going to be a hassle. Therefore, the planning Department, the class use a list, and the department input, it corresponds to the list of the Department's class, rather than all the school class.

After experimenting, I used the definition sequence to get this problem resolved quickly, and the data input work was much easier.

First, the establishment of the basic data table

First we select a worksheet, double-click its sheet tab, and rename it to "base data." Then the class names in the four sections are entered in the cells in columns A to D, each one in a single column. The name of the section can be placed on the top row of the column. In E1 cell enter "department", and in its lower cells, respectively, entered the name of each department. As shown in Figure 1.

Figure 1

Select the cell range of column A, and then click the Small triangle to the right of the name feature group definition name button defined by the Ribbon Formula tab, and select the Define name command in the pop-up menu to open the New Name dialog box, as shown in Figure 2. Confirm that the dialog box is closed by pressing the OK button after the name input column for the "Computer section". This allows you to define the column a cell as a "computer section."

Figure 2

In the same way, the columns of B, C and D are selected, and the corresponding cell areas are defined by each section name respectively.

Select the column E of the section and define the column area as "department".

Second, the use of sequence to achieve rapid and accurate entry

Now go back to the target worksheet, taking the table shown in Figure 3 as an example. Select the B2:b6 cell range and click the Data Validation button in the Ribbon Data tab Data Tools feature Group, the small triangle in the lower-right corner, select data Validation in the pop-up menu, and open the Data Validation dialog box.

Figure 3

Click the Settings tab in the dialog box, select Sequence in the Allow Drop-down list, and enter =indirect ($B) in the input box under source, as shown in Figure 4.

Figure 4

Select the C2:c6 cell range, and still open the Data Validation dialog box. All settings are the same as shown in Figure 4, which is to change the formula in source to "=indirect ($B 2)". Be aware of the differences in how you refer to formulas.

After this setting, when we position the mouse over a cell in the B2:b6 area, a Drop-down button appears to the right of the cell, and we can choose to enter the department name in this drop-down list. When we click the Drop-down button for a cell in the C2:c6 area, the list of classes in the current section is displayed, as shown in Figure 5.

Figure 5

With this method, the data can be inputted quickly and accurately, which is convenient and effective.

Note : More wonderful tutorials Please pay attention to the triple Office software tutorial section, triple Computer office group: 189034526 welcome you to join