When using Microsoft Excel software for calculations, there are times when you need to reference cells or ranges indirectly. This can be done by using the Indirect function, which allows you to refer to other cells or ranges in a worksheet or across multiple worksheets. Explore the formula and different ways to use the Indirect function with FPT Shop through the following article.
An overview of the Indirect function
The Indirect function is a commonly used function in Excel that allows users to indirectly refer to selected objects. These objects can be cells, worksheets, or even entire workbooks. Moreover, users can use the Indirect function to reference objects from one worksheet to another.
The syntax of the Indirect function in Excel is: =INDIRECT(ref_text, [a1])
Explanation of the components in the above formula:
- ref_text: The object that needs to be referenced, a required component when using the function. This component can be a cell or a named range.
- a1: An optional value, representing the reference type of the ref_text component. This component can be omitted to simplify the function.
/fptshop.com.vn/uploads/images/tin-tuc/172869/Originals/ham-<a href='https://meo.tips/health-and-life/the-ultimate-guide-to-lightning-safety-protect-yourself-from-natures-fury/' rel='dofollow' class='onpage-seo' >indirect</a>-1.jpg”></p>
<h2><strong>How to use the Indirect function in Microsoft Excel</strong></h2>
<p>To help you better understand the various ways to use the Indirect function, you can refer to the examples shared by FPT Shop below. Each usage case will come with an illustrative example.</p>
<h3><strong>Direct referencing with the Indirect function</strong></h3>
<p>Creating direct references using the Indirect function is not as difficult as you might think. For example, suppose we have a data table (Table 01) with two columns containing values in column A and column B. The problem requires the user to use the function to reference the values of the calculation cells in column B to return the values in column C.</p>
<p>In cell C2, enter the Indirect function as follows: =INDIRECT(B2). Then, gently drag the formula at cell C2 to the remaining cells in column C, and the desired results will appear.</p>
<p>Explanation of the result:</p>
<p>The entered formula =INDIRECT(B2) will reference cell B2 (which has the value A2), and then continue to reference cell A2 in the worksheet (which has the value 10). From there, it returns the result value in cell C2 as 10. The operation of the function in other cells in column C can be understood in a similar way.</p>
<h3><strong>Indirect referencing with the Indirect function</strong></h3>
<p>In addition to direct referencing as shown above, users can also use the Indirect function to create indirect references.</p>
<p>Illustrative example of how to use this method: We are also given a data table (Table 01) with two calculation columns containing values in column A and column B. In cell C2, enter the following function: =INDIRECT(“A”&B2), in which the explanation is:</p>
<ul>
<li>A is the character representing column A in the Excel worksheet.</li>
<li>B2 is the cell to be referenced and has a value of 2.</li>
</ul>
<p>As you can see, this calculation function will reference cell A2 (which has the value 10) and return the result to cell C2. Then, gently drag the formula for the remaining cells in column C, and the desired results will be displayed.</p>
<p><strong></strong></p>
<h3>Using Indirect to reference pre-named ranges</h3>
<p>In addition to using the Indirect function to reference specified cells, you can also use this function to reference pre-named ranges.</p>
<p>Illustrative example: Suppose we have a table with sales data (Table 01) for 3 products, A, B, and C. You are required to calculate the total and average revenue for each product.</p>
<p>Taking the calculation sample for product A in the table, we apply the Indirect function in Excel as follows:</p>
<p>To calculate the total revenue achieved, in cell G2, you need to enter the following function: =SUM(INDIRECT(G1))</p>
<p>To calculate the average value of revenue, in cell G3, enter the following function: =AVERAGE(INDIRECT(G1))</p>
<p>Explanation of the components of the function:</p>
<p>The cell G1 has the value SPA, which is a named range that has been pre-defined. Specifically, the named range in the worksheet is the value of product A, the range B2:B6 in the data table.</p>
<p>Similarly, when you want to calculate the total and average revenue for product B and product C, you just need to apply the Indirect function in a similar manner, and the results will be as follows:</p>
<h3><strong>Using Indirect to reference cells on a different worksheet within the file</strong></h3>
<p>When you want to reference an object from one worksheet to another, you can use the Indirect function as follows:</p>
<p>=INDIRECT(“” & Sheet’s name & “!” & Cell to pull data from)</p>
<p>Explanation of the components in the above function:</p>
<ul>
<li>Sheet’s name: The name of the worksheet that the user wants to refer to.</li>
<li>Cell to pull data from: The cell in Sheet’s name that the user wants to reference.</li>
</ul>
<p>Illustrative example: Suppose we have a data table called Table 01 located in Sheet 02. You are required to return the value from column A (Table 01 in Sheet 02) to column C (Table 01 in Sheet 01).</p>
<p>Perform the following steps: In cell C2 of Sheet 01, the user enters the Indirect function as follows: =INDIRECT(“”&$E$2&”!”&B2), and this is the result that the user receives:</p>
<h3><strong>Combining with other Excel functions</strong></h3>
<p>Furthermore, you can combine the Indirect function in Excel with other related functions, such as:</p>
<p>Using the Address function in combination with Indirect to reference an object based on the address of any cell. For illustrative purposes, enter the following function in cell C1: =INDIRECT (ADDRESS(4,2)) and this is the result you will get:</p>
<p>Explanation of the function’s meaning: The Address function is used to refer to the address of cell A4B2, and the Indirect function continues to reference that cell and returns the result as shown in the image below:</p>
<p><h2><strong>Conclusion</strong></h2>
<p>In summary, the above article has shared detailed information about the Indirect function, including its common use cases, meaning, and accompanying illustrative examples. It is hoped that with this useful information, you will be able to use the Indirect function proficiently and apply it flexibly for referencing purposes.</p>
<div class='code-block code-block-1' style='margin: 8px auto; text-align: center; display: block; clear: both;'>
<script async src=){kind=spacer}
