We will see a dynamic way to get the result in the below example. We can ignore the last argument since we provide an A1-style reference. The result in cell F1 is 41, which is the value we have in cell C9.
But before I get into the examples, let’s first have a look at its syntax. The INDIRECT function in Excel creates an indirect reference to a cell, sheet, or workbook. It is designed to teach you these and (many more Excel functions) in the shortest time.
In this Excel tutorial, I will show you how to use the indirect function in Excel using some practical examples. Even if you add, move or https://viamrkting.com/what-we-do/marketing-strategy-and-gtm-planning/ delete cells before or after a named range (Expenses), the answer to the INDIRECT function won’t change. The INDIRECT function can not only recognize cell references and ranges but named ranges too.
- It returns one reference per call, so each formula gives you a single result.
- In this video we’ll look at how to create a dynamic reference to a worksheet in a formula.
- We will try to retrieve the value from cell A4 by entering the below formula in cell C2.
- Both forms follow specific grammatical rules involving punctuation, tense, pronouns, and expressions of time and place.
How to use the INDIRECT function
Assume we need to get the total sales for the product “Banana” using the INDIRECT function. Below are the monthly sales of products in a department store. INDIRECT function in Excel helps users reference cells, ranges or arrays, and even worksheets and workbooks indirectly.
The dependent drop-down list is nothing but a list https://www.e-lib.info/finding-ways-to-keep-up-with-8/ of one cell depending on the value entered in the other cell. Similarly, we can do dynamic calculations for each product. Now, we can use this named range in the INDIRECT function. For instance, we can enter “Product_Sales” for the above table.
Example 4: Calculate the SUM of a Range of Cells
The INDIRECT function can easily be used with named ranges. Because the text value is static, the reference created by INDIRECT will not change even when cells, rows, or columns are inserted or deleted. Unless you define the format of the cell reference in the 2nd argument, the default format will be A1 style. In C6 and C7, enter the names of the Excel workbook and the spreadsheet from which data will be extracted.
INDIRECT turns that into a reference to B4, which holds 5600, James Wilson’s sales. I want to pull the sales figure from whatever row number you put in D2. Column A lists employees, column B lists their sales, and cell D2 holds a row number. The formula never changes, only the text it reads. Change D2 to “B5” and the result updates https://biznisnovine.com/the-5-rules-of-and-how-learn-more-2/ to the value in B5.
Different reporting verbs are used depending on the type of sentence. In indirect speech, they are usually converted into an infinitive form using “to” plus the base verb. The word order becomes that of a normal statement, and auxiliary verbs are adjusted accordingly. In indirect speech, they are usually introduced with the word “that.” This shift in tense helps indicate that the statement is being reported after it was originally spoken.
Present forms typically change to past forms, and past forms may change to past perfect. When the reporting verb is in the past tense, the tense of the original sentence usually shifts backward. In this example, “I” changes to “she” and “my” changes to “her” to match the reporting context. Pronouns must be changed according to the perspective of the speaker and the listener.