Like and see, form a habit; I do not know the depth of the river until I come to it.
Wechat search [Yixin Excel] to pay attention to this different we-media person.
In this paper, making github.com/hugogoos/Ex… Has been included, including Excel system learning guide series of articles, as well as a variety of Excel materials.
Sometimes for data entry specifications, we use drop-down lists to give optional values, such as whether, male/female, level, department, etc., can only enter given values.
Today xiaobian will take the provincial and urban three-level linkage drop-down list as an example, from the first level drop-down list, the second level linkage drop-down list, the third level linkage drop-down list, one for you to explain.
1. Level 1 drop-down list
I think we have seen a lot of drop-down list, the following specific steps:
① Create a “Source Data” worksheet to save the source data in the drop-down list, and create a column of provincial data;
② Select the cell area to display the drop-down list;
③ Select “Data” TAB, click “Data Validation” in “Data Tools” to open the data validation dialog box;
④ In the data verification dialog box, set “Settings” → “Verification Conditions”, “Allow” is “sequence”, and “source” is the provincial data of the “source data” worksheet;
⑤ At this point, an inverted triangle drop-down option symbol will be displayed in the lower right corner of the cell that needs to display the drop-down list, and you are done.
If the text doesn’t look very clear, let’s look at the dynamic operation:
2. Secondary linkage drop-down list
The level-2 linkage drop-down list is not a simple creation of two level-1 drop-down lists. Because it is linked, the level-2 drop-down list automatically changes according to the selection of the level-1 drop-down list. Therefore, the secondary linkage drop-down list is made based on the primary drop-down list and combined with the “Definition name” in the “Formula” TAB and the INDIRECT function.
The production steps will be much more complex than the first level list, the following small series to make the second level linkage drop-down list step by step:
(1) In the “source data” worksheet to prepare the city drop-down list source data: put all the provinces and cities in the secondary drop-down list in the city data, the first province in the city data, the corresponding city below;
② Select the city data area in the “Source Data” worksheet;
③ Click “Conditional Location” in “Find and Select” under “Start” menu to bring up the “conditional Location” dialog box;
④ You can also press F5 or Ctrl+G to bring up the “Positioning” dialog box and then click “Conditional positioning” to bring up the “conditional positioning” dialog box;
⑤ Select “Constant” and click OK;
⑥ Select “Formula” TAB, click “Defined name” in “create from selected content” and select “first line”;
⑦ Cut to the “secondary linkage drop-down list” worksheet, select the cell to display the secondary list;
Select “Data” → “data Tools” → “Data Validation” → “Settings” → “Validation Criteria” and “Allow” to “sequence” and “source” to “=INDIRECT(B5)”. B5 is the corresponding area of the first-level drop-down list.
⑨ Now we are done.
Let’s take a look at the specific operation process:
3. Three-level linkage drop-down list
The method of making a three-level linkage drop-down list is the same as the method of making a two-level linkage drop-down list. The difference is that a three-level data source is required and the formula of “source” is changed to “=INDIRECT(C5)”. The specific production process is not described in detail here. The following production effect diagram is given:
Function analysis
An INDIRECT function
Function: Returns a reference specified by a text string
Parameters: cell address or cell name
Example: INDIRECT(B5), a reference to the cell content represented by “B5” that returns “Zhejiang Province”
The INDIRECT function is explained and used in more detail in a later series of functions. I won’t go into it here.
If you are interested in the feature, you can tell xiaobian oh, xiaobian will write a corresponding article for you. Of course, it’s first come, first written. I will make a schedule and try to meet everyone’s needs. So if the next article is not what you want, please don’t worry, it may be the next one. Let me know what you want to learn.
This article continues to update, you can search wechat “yixin Excel” first time to read, this article GitHub github.com/hugogoos/Ex… Excel System Learning Guide series, welcome Star.
