The drop-down menu of the form was originally made like this!

The customer service staff receives a lot of business consulting information every day, and needs to record the customer name, consulting problem, etc. one by one. If some customer names are already stored in the Excel worksheet, if you want to enter part of the customer's name when recording, Ability to display the full name of all customers including the part name in the resulting pull-down menu for selection, while also supporting pull-down menu dynamic updates (ie, when adding a new customer name in the customer's full name table, the drop-down menu also follows Update) (figure 1) How can a worksheet with this feature be implemented? The following is the specific implementation method in Excel 2016.

1. Realize drop-down menu dynamic expansion

In the Excel worksheet, in general, when creating a drop-down menu, select the corresponding cell and click “Data Verification” in the “Data” tab. In the “Settings” tab of the pop-up window, “Verify the conditions → Allow” Select "Sequence" at the source, select the content of the corresponding cell at the source or enter the list entry to be displayed (list entries are separated by commas in the English state) so that the drop-down menu is created when new content is added under the selected cell The drop-down list does not increase, which is not ideal in actual use. To make the drop-down menu dynamically expand based on what has been added, you can use function-matching data validation.

First, click on "Define Name" in the "Formula" tab. In the pop-up window, enter "Customer's Full Name". In the reference field, enter "=offset(Customer Name!$A$2,,,counta(Customer Name! $A:$A)-1)”; Then, select the corresponding cell (for example, D2:D27), click “Data Verification”, select “Sequence” at “Verification Condition → Permit”, and point the cursor to the source At this point, press F3 and select "Customer's full name" in the pop-up window. In this way, when the customer name is added or deleted in column A, the drop-down list is also updated dynamically (Figure 2).

hint:

There is also a way to achieve dynamic expansion of pull-down menus: First, select the customer name data, click on the "Table" in the "Insert" tab to confirm; select the customer name data again, and click on the "Formula" tab "Defined Names" Create "based on the selected content", check the "First line" in the pop-up window, determine; Similarly, select the corresponding cell (such as D2: D27), click "Data verification", in the "Verification conditions → permission" Select "Sequence", point the cursor to the source, press F3, and select "Customer Name" in the pop-up window. In this way, when the customer name is added or deleted in column A, the drop-down list is also dynamically updated (Figure 3).

2. Smartly get the current input

In general, when the customer name is recorded, the customer's full name is not entered. This requires the worksheet to capture the input content in time. Next, explain how the worksheet intelligently captures the current input. Enter "=CELL("contents")" in cell F1 of the worksheet. A warning message will appear after the carriage return. It will be directly determined that the value of cell F1 will become 0. At this time, input the content in any cell, F1 The cell will capture the input content (Figure 4).

3. Dynamic filter extraction data

Create a new worksheet named "Customer List", click "Name Manager" in the "Formula" tab, and click "New" in the pop-up window. Enter the "Customer List" at the name of the new pop-up window. Enter "=offset(customer list!$A$2,,,countif("full","*"&cell("contents")&"*"))"; select cell A2 of the "customer list" worksheet Enter "=INDEX(Customer Name!A:A, SMALL(IF(ISNUMBER(FIND(CELL("contents"), full customer name)), ROW (full customer name), 4^8), ROW() in the formula edit field. A1)))&"", press Crtl+Shift+Enter at the same time, when the warning prompt pops up, you can directly determine; fill the array formula to A80 (fill the filter result according to the record in the customer name), This will add the full name of the customer selected in the "Customer Name" worksheet based on the input keyword to A2 and the following cells in the "Customer List" (Figure 5).

Function explanation:

(1) "=offset(customer list!$A$2,,,countif("full","*"&cell("contents")&"*"))", countif is counted in the customer's full name according to the input. The number of records of the content, offset is filtered from the customer list to meet the conditions of the record;

(2) "=INDEX (Customer Name!A:A, SMALL(IF(ISNUMBER(FIND(CELL("contents"), full customer name)), ROW (full customer name), 4^8), ROW(A1)))& """, FIND is based on the input in the full name of the customer to find, if found to return to the appropriate location, can not be found to return an error, in order to be compatible with this error need to use ISNUMBER, that is, if found to return TRUE, can not find FALSE is returned; ROW is found if you return to the corresponding row of the record; SMALL can extract the record found one by one.

Finally, in the "Incoming Call Log" worksheet, select the column "Customer's Full Name" and click "Data Verification" in the "Data" tab. In the "Settings" tab of the popup window, select "Verification Conditions → Permit" "Sequence", point the cursor to the source, press the F3 key, select "Customer List" in the pop-up paste name window; in the "error warning" window, remove the checkmark in front of "Error warning when entering invalid data" ( Figure 6). In this way, the entire intelligent form is completed.