Next, we will use the "development tool" of Excel20 16. Some people's Excel may not display this item by default, which requires simple setting. Click "File → Options". In the options window that opens, switch to the "Custom Ribbon" on the left, then find the "Development Tool" item on the right and select it.

First, make a foreground query interface. The interface can be simple and clear. For example, there is only one line header and one blank line. You can set it according to your actual needs. Bian Xiao simulated a simple unit personnel query table here.

Next, do the background database. Database tables can be placed far away from the foreground, or even in another worksheet to achieve the effect of hiding. Here, for the convenience of explanation, Bian Xiao arranges two tables together, and the header is exactly the same as the front desk table, except that a lot of data information is filled in below.

Next, make a drop-down list box. Switch to the Development Tools tab, click Insert, and then click Combo Box in the pop-up menu.

At this point, the cursor becomes a cross, and you can draw an icon of a drop-down list control in any blank space of the worksheet. Right-click the control and select Format Control from the right-click menu.

In the open formatting control window, click the input box behind the data source area, and then select all the data in the work number column in the background data in the worksheet, and the labels in this area will be automatically entered. Click the input method behind the cell link with the mouse, and then click in any blank cell of the worksheet to set the linked cell, in this case, B9 cell.

The number of items displayed in the drop-down box is 8 by default, and will not be changed here. Click the "OK" button directly to return. At this time, click the drop-down button, and all the items in the "Work Number" column will be displayed, which can be selected at will. The number of columns selected will be displayed in the linked cells.

In the foreground interface table, select the first cell under the task number, in this case, cell B4, and enter the following formula in this cell:

= indirect+10, column))

The main function of indirect function is to reference the contents of the specified cell, and its parameters indicate the specific location of the referenced cell. In this example, "ADDRESS+ 10, COLUMN", B9 is the linked cell of the drop-down list made earlier, showing the number of rows in the drop-down list, and 10 is the distance from the background data table to the top of the worksheet. The data of the corresponding cell in the background data table is obtained by adding them together, which is used as the parameter of the indirect function.

After entering the formula, drag it to the other five cells. Finally, drag and drop the drop-down list control made above into the first data cell in the foreground interface to overwrite the original cell, and adjust the size to be the same as the cell.

At this point, a dynamic query table is completed. Click the drop-down menu to select any job number, and the system will automatically display the employee information corresponding to the job number.