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

Original title: "Make drop-down list must use, this Excel strongest reference function, learn in 1 minute!"

Hi everyone, I am Tian Tian who loves to learn Excel ~

It's time to start classes on time every week ~

Today we'll learn a very useful address reference function--the indirect function.

The main function of this function is to return the content pointed to by the address according to the text address.

To use it well, you only need to remember one phrase:

What's the address?

2. In the basic usage case table, there are different contents written. Now we want to return the content pointed to by the address according to the cell address, and we can use the indirect function.

Enter = INDIRECT in cell [C4] and put parentheses "(").

The address here we want to use text to describe, so first put double quotes "", in double quotes enter [A3], put back brackets ")", press the Enter key, you can see,[A3] cell "Autumn Leaf PS" is displayed.

The complete formula is as follows:

=INDIRECT("A3")3, dynamic address students will feel strange, this is not unnecessary! Wouldn't typing "=A3" directly into this cell get the same result?

The value of the indirect function lies not in whether it finds the result, but in how it finds it.

To be more precise: this function recognizes a text string as an address, which is where it gets really good.

Let's look at the example. In the cell [D1], there is a letter "A". In the cell [D2], there is a number 1. Now we have to combine the contents of these two cells together to form a cell address. How to do it?

Enter the equal sign in cell D4, select cell D1, enter the text connector "&", select cell D2, press Enter, and we create an address.

Because we use the formula to disassemble the function key of this address, when I change the contents of cell [D1] or cell [D2], this address will change accordingly, so this is a dynamic address (B2).

What if I wanted to display the contents of this dynamic address directly here?

Just add an indirect function after the equal sign "=", wrap the text behind it in parentheses, and then press the Enter key to see what it displays, which is the content in the cell [B2].

The complete formula is as follows:

=INDIRECT(D1 & D2) This way, when we modify the address, we can dynamically query the contents of this area.

3. Create a secondary drop-down list case To create a drop-down list in the commodity name column, and hope that the options in the commodity name drop-down list can change according to the change of commodity category.

Select the whole table area, click the [Create according to selected content] button under the Formula tab. Since the title is in the first row, you only need to check the front of the first row and click OK.

Then we select the commodity name column, click the [Data Validation] button under the Data tab, set the [Validation Conditions] to [Sequence], and enter the formula: =INDIRECT function in the source, with brackets "(".

In parentheses, we directly refer to the [A10] cell on the left, remove the two dollar signs "$" here, and that's it.

The complete formula is as follows:

= DIRECT (A10) When the commodity category is fruit, the commodity name corresponds to the fruit option, and when the commodity category is seasoning, the commodity name corresponds to the seasoning option.

Have you learned all the skills of indirect function? Hurry up and try it ~

This article comes from Weixin Official Accounts: Autumn Leaf Excel (ID: excel100), author: Tian Tian

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.