How Excel makes query tables quickly

how Excel makes query tables quickly

Now we're going to use the "Development tools" for Excel 2016, and by default some people's Excel may not show this one and need to be set up briefly. Click "file → options", in the Open Options window, switch to the "custom Ribbon" on the left, and then to the right to find the "development tool" item, check it out on it.

First of all, create a front-end query interface, this interface can be concise and clear, such as only a line of table header plus a blank line, each person according to their actual needs to set. The small weave here simulates a simple Unit personnel inquiry form.

Next, make a background database. Database tables can be placed far away from the foreground, and can even be placed on another worksheet to achieve hidden effects. Here to facilitate the explanation, the small set of two tables arranged together, its header and the front table is exactly the same, but the following filled with a large number of data information.

Next, make a drop-down list box. Switch to the Developer tab, click Insert, and click the combo box (form control) in the pop-up menu.

At this point the cursor changes to the "ten" font, and you can draw an icon for a Drop-down list control in any blank space on the worksheet. Right-click the control and choose Set control Format from the right-click menu.

In the Open Settings Control Format window, click the input box after the data source range, and then in the worksheet, select all the data in the "Work number" column in the background data and automatically enter the label for that area. Click on the input method after the cell link, and then click in any blank cell in the worksheet to set the linked cell, in this case, the B9 cell.

The Drop-down display number defaults to 8, and no changes are made here. Click on the "OK" button to return directly. Click the Drop-down button at this point to display all items in the work Number column, and you can choose any option. The number of columns selected is displayed in the link cell.

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

=indirect (Address (INDIRECT ("B9") +10,column (B11))

The main function of the indirect function is to refer to the contents of a specified cell, and its arguments indicate the exact location of the cell to reference. In this example, "Address" (INDIRECT ("B9") +10,column (B11)), B9 is the linked cell of the previously made Drop-down list, showing the number of rows in the Drop-down list, 10 is the number of rows between the background data table and the top of the worksheet. This adds up to the data in the background data table that corresponds to the cell, and is used as a parameter to the indirect function.

After you have entered the formula, copy it to another 5 cells by dragging it. Finally, drag the previously made Drop-down list control to the first data cell in the foreground interface to overwrite the original cell and resize it to the same size as the cell.

At this point, a dynamic query table is done, click the Drop-down menu to select any work number, the work number of the corresponding employee information will automatically show up.