Shulou(Shulou.com)11/24 Report--

The original title: "refuse to work overtime!" This Excel drop-down list is a super complete collection you deserve! "

Today, let's talk about the things about the Excel drop-down list.

Level 1 drop-down list A simple data validation to fix the level 1 drop-down list.

The idea is to put the classified items separately in a parameter table, and then reference these parameters as data sources through [data validation].

The specific setting method is as follows:

❶ opens the data validation window.

Select [sequence] for ❷ verification condition.

❸ chooses to add data sources.

For relatively fixed classification information such as product types, departments, provinces and cities, we can use the drop-down list to limit the input, so as to avoid the occurrence of one classification and multiple writing methods.

The second-level drop-down list defines the name + data validation + INDIRECT function to easily create the second-level drop-down list.

The so-called level 2 drop-down list is a level 2 list option that can be dynamically updated based on level 1 data.

The specific setting method is as follows:

❶ prepares secondary drop-down list data

❷ definition name Select all the list data, click the "Formula" tab, find the "defined name"-[create based on the selection]:

In the pop-up window, check the first line and click OK.

This gives the secondary content an overall name, which is the content of the "first line" cell. For example:

❸ to create a drop-down list first set up a drop-down list, the specific operation mentioned earlier, will not be repeated here.

The steps for the secondary and primary drop-down lists are more or less the same, except that you need to use the Indirect function when selecting the source:

PS: prompt "the source currently contains an error" because there is no data in the "first-level list" cell referenced by the "secondary list", resulting in the source error, ignore it, and click "Yes".

Knock on the blackboard:

The = indirect (A2) Indirect function is an indirect reference function that returns the reference specified by the text string.

For example, the A2 cell is referenced here, but the result returned is the value in the parameter table C2:C5 cell. That is, refer to the municipal level contained in the provinces in column C.

Three-level drop-down list three-level drop-down list is actually not as difficult as most friends think. If you can learn first-level and second-level lists, I'm sure this tip won't stop you.

Compared with the former two, the biggest difference of the three-level drop-down list is the data source.

The header of the third-level content is connected by one or two levels. Looking at the picture is more intuitive, ↓.

The specific setting method is as follows:

❶ defines name selection list data and uses the location function to quickly select all non-empty cells.

Then use the "create according to selection" feature in the "Formula" tab to create a batch of custom names for the city selection corresponding to the province.

Oh no, it looks like the car overturned.

Excel prompts "this selection is not valid".

When we take a closer look at the table, we find that there is one area that is obviously not connected to other areas, which is why the error was reported, and Excel re-identified and selected a new area.

This is due to the mechanism of Excel itself. If two consecutive columns have the same number of rows and the number of subsequent columns is smaller than their number of rows, an error will be reported.

If you don't believe me, let's adjust the column order slightly to separate the two columns ↓

Then try [define name] again.

Finally, check:

Set up successfully!

❷ creates a three-level drop-down list. At this point, you still need to use the [Indirect] function, but it's a little different, the formula:

= Indirect (A2&B2) means that both the primary list and the secondary list are referenced.

Isn't it easy!

The drop-down list at level 1, 2 and 3 only involves a few very basic knowledge points:

❶ data validation

❷ definition name

❸ Indirect function

After reading this article and practicing again, you must have mastered seven, seven, eight, eight.

But the search drop-down list I'm going to talk about is a little bit more difficult.

Search drop-down list when you type the keyword [Akiba Excel] in the Wechat search box and make sure, you can search out all the content related to [Akiba Excel] on the platform.

The same is true of the search drop-down list, although not as powerful as Wechat search, but it can be achieved in Excel through the search keywords, find set, fixed data, quickly select and enter the form.

Specific setting method:

❶ creates an auxiliary column based on keywords, ①, and fill in the complete province list in column A.

② creates secondary columns that are filtered by keywords:

Fill the following formula into the B2 cell, use the [CTRL+SHIFT+ENTER] key combination to end the formula, and fill it down.

Formula:

= IFERROR (IFERROR (MATCH (IF (FIND (CELL ("contents"), $A$2:$A$35) 0 (MATCH (FIND (CELL ("contents"), $A$2:$A$35) 0), (), (), ROW (A1)),))

Although the formula is long and difficult, it can be applied directly.

The method is simple:

Because the four blue parts of the formula are exactly the same: $A$2:$A$35, which is the cell where the complete list of provinces resides.

So, just replace the blue part with the list area you want to do!

❷ defines the secondary column name ①. Click the "Formula" tab-"name Manager"-- create a new name.

② new name. Enter "province list" for the named area, and enter the formula by referencing the location:

= OFFSET (Sheet1century Boulder 2 Magazine 0lemagogic Code COUNTA (Sheet1mm Blee2VuitsBLY 35)-COUNTIF (Sheet1ZHANG2VOLING BLING 35,), 1)

Although the formula used is very long, the advantage is that the version of Excel is not too demanding, and both versions of Office2007 and WPS can be used.

PS: if it is WPS2019 or above, it comes with "search drop-down list" ~

❸ Settings drop-down list Select the cell of the drop-down list that needs to be set, open the data validation window, select "sequence" in the permission of the verification conditions dialog box, and fill in the source "= province list".

Click the "error warning" tab and uncheck "Show error warning when entering invalid data".

Done!

If you just want to know how to set up a search drop-down list, learn the above is enough

Finally, it is not easy to see the students here. I hope everyone can successfully master the above knowledge points, improve efficiency, reduce work pressure, and embrace life!

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Zhu Lan, Editor: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.