How to Use the INDIRECT Function in Excel 12 Examples

Read More Opinions expressed in the examples do not represent those of Merriam-Webster or its editors. “He said that he would call later,” is an example of indirect speech since his actual words were “I’ll call later.” an indirect question such as “She asked whether the doctor had arrived” See More Looking at her watch was her indirect way of telling him it was time to leave. Try it out with the examples above and see where it fits into your own work. This is handy when the column stays the same but the row you want changes based on another input.

Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Here we have a simple table that summarizes sales by salesperson over a four-month period. In this video we’ll look at how to create a dynamic reference to a named range using the INDIRECT function. In this video we’ll look at how to create a dynamic reference https://www.suscinio.info/a-quick-overlook-of-your-cheatsheet-6/ to a worksheet in a formula.

This is not specific to the INDIRECT function; the same limitation is true in all formulas. https://czdc.info/sunderland-afc-social-media-fan-engagement If the sheet names are changed, the formula will automatically recalculate. The formula in C5 concatenates the text in B5 to the string “!A1” and returns the result to INDIRECT. The text entered in column E represents different ranges.

Example 1 – Using the INDIRECT Function to Convert a Text String into Cell Reference

The worksheet below shows the basic idea of the INDIRECT function. The second argument, a1, is optional and indicates the “style” of the reference provided. INDIRECT is a volatile function and can cause performance issues in large or complex worksheets. The problem, however, is that once you https://fu-fu-nikki.com/2019/10/ have created a reference as text, Excel won’t recognize it as a reference.

Interrogative Sentences

For commands and requests, verbs such as told, ordered, requested, and advised are appropriate. For questions, verbs like asked, inquired, and wanted to know are used. For statements, verbs such as said, told, explained, and mentioned are common.

Types of Sentences in Direct and Indirect Speech

If the reported statement expresses a universal truth or a general fact, the tense does not change even in indirect speech. Indirect speech does not use quotation marks and usually involves changes in tense, pronouns, and expressions of time and place. Sometimes you want to reference a worksheet dynamically in a formula, so it can be easily changed. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

• Learners often make errors when converting direct speech into indirect speech.
• Below are the monthly sales of products in a department store.
• For statements, verbs such as said, told, explained, and mentioned are common.
• Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed.
• If the sheet names are changed, the formula will automatically recalculate.

The INDIRECT function allows users to indirectly reference cells, ranges, arrays, and even worksheets and workbooks. We have got the all the month’s sales value for the product “Carrot”. If we want to get the sum for another product, we must manually change the reference. The resulting value of 11,422 is the total of the product Banana.

We have created a reference to Cell B2 (that contains the text string A2). The INDIRECT crates an indirect reference to cells, ranges, sheets, and workbooks, too 🔄 As you continue reading, do not forget to download our free sample workbook that’ll help you tag along with the guide. Direct speech preserves the speaker’s exact words, while indirect speech focuses on conveying the meaning in a grammatically appropriate form. Learners often make errors when converting direct speech into indirect speech. For exclamations, verbs like exclaimed, shouted, cried, and cheered are used to convey emotion.

Example 2 – Using the Ampersand to Define the Row and Column Index with the INDIRECT Function

Begin with formulas, charts, shortcuts, and real spreadsheet practice. Explore practical ExcelMojo courses built for spreadsheet productivity, dashboards, automation, and business analysis. For instance, look at the below example.In cell F2, we have the worksheet name, and in cell G2, we have the cell address of this worksheet. The INDIRECT function is used to get the reference from another worksheet. Similarly, we can change the country name in the E2 cell to show the corresponding country players list in the F2 cell. But this drop-down should depend on the country drop-down created in cell E2.

Tips & Common Mistakes

The below example shows how to calculate the average using the named ranges. If you have created a named range in Excel, you can refer to that named range using the INDIRECT function. You can also use the INDIRECT formula to refer to a cell in some other worksheet or another workbook as well. Note that you don’t need to use double quotes here as the C1 has the cell reference stored in the text string format only. You can also use the INDIRECT function when you want to lock the cell references in such a way that it does not change when you insert rows/columns in the worksheet. The above formula takes the cell reference A1 as the input argument (within double quotes as a text string) and returns the value in this cell, which is 123.

Knowing where formulas are is the first step in understanding how a spreadsheet works. This technique is most often used in array formulas that need a numeric array for processing of some kind. To increment a reference created as text inside the INDIRECT function, you can use the CELL function. You may also run into the ROW + INDIRECT approach in more complex formulas that need to assemble a numeric array “on the fly”. In the worksheet below, VLOOKUP is used to get costs for two vendors, A and B.