The Excel Indirect Function plays a vital role in creating complicated formulas that reference cell addresses indirectly. It simplifies complex formulas, consolidates data from multiple sources, and enhances efficiency in custom reports and dashboards.
People may hesitate to use the Indirect Function in Excel due to its complexity and unfamiliarity. This article will introduce some easy ways to use indirect function.
Part 1: What Is The Indirect Function In Excel?
The Indirect Function in Excel is a versatile tool that allows users to create dynamic cell references indirectly within formulas. Instead of directly referencing a specific cell address, the Indirect Function takes a text string as an argument and interprets that string as a cell reference. This feature allows building formulas that automatically adjust and adapt based on changing data or conditions without requiring manual adjustments.
The syntax of the Excel Indirect Function is as follows:
INDIRECT (ref_text, [a1])
ref_text: is a required argument and represents the address of a cell, a named range, or a reference returned as text from another formula.
[a1]: it's an optional argument that specifies the type of reference style to use. If a1 is TRUE or omitted, the function interprets ref_text as an A1-style reference (e.g., "A1" refers to cell A1). If a1 is FALSE, the function interprets ref_text as an R1C1-style reference (e.g., "R1C1" refers to the cell in the first row and first column). Most users typically use a1 as TRUE.
Part 2. 3 Easy Ways to Use Indirect Function in Excel
By using the indirect function, we can generate custom reports and financial models that automatically update when new data is added or modified. The Excel INDIRECT function is particularly useful when working with large datasets such as multiple sheets or workbooks in an Organization.
We will discuss a step-by-step tutorial with 3 different examples on how to use the INDIRECT function in an Excel Sheet:
Example 1: How to use the INDIRECT function in a single sheet of Excel:
Step 1: Open Excel and create a new or existing workbook where you want to use the INDIRECT function.
Step 2: Let’s enter some data into the following Cells A1 to A3:
A1: 20
A2: 40
A3: 60
Step 3: In a different cell, let's say cell B1, enter the following formula=INDIRECT("A1")
Step 4: Press Enter. Cell B1 should now display the value ‘20’ because the INDIRECT function takes the text string "A1" as a reference and fetches the value from cell A1.
Step 5: Now, we will make the usage of the INDIRECT function in a more advanced manner. Let’s enter the number ‘2’ in Cell C1. Now C1 will act as our ‘row number’ for the INDIRECT function.
Step 6: In cell D1, enter the following formula=INDIRECT("A" & C1)
Step 7: Press Enter. Cell D1 should now display the value ‘40’ because the INDIRECT function combines the text "A" with the value in cell C1 (which is 2) to create the reference "A2" and fetches the value from cell A2.
Step 8: We can further test the INDIRECT function dynamically by changing the values in Cell C1. For example, if we change C1 to 3, Cell D1 will now display the value 60 (as it will fetch the value from Cell A3).
Congratulations! You've used the INDIRECT function to reference cells within the same sheet in Excel dynamically. This capability can be handy when you want to create dynamic formulas that adapt to changes in your data.
Example 2: How to use the indirect function with different Excel functions
In this tutorial, we will learn the usage of the INDIRECT formula in combination with different Excel functions. Let's go through the process:
Step 1: Open Excel and create a new workbook.
Step 2: Let’s say, we are going to enter the following Data in Cells A1 to A3:
A1: 5
A2: 10
A3: 15
Step 3: In cell B1, enter the number 2. This will be our row number for the INDIRECT function.
Step 4: In cell C1, enter the following formula=INDIRECT("A" & B1)
Step 5: Press Enter. Cell C1 will now display the value 10 because the INDIRECT function combines the Column"A" with the Row Number in cell B1 (which is 2) to create the reference "A2" and fetches the value from cell A2 (which is 10).
After applying our basic INDIRECT function, we will try to use it with different Excel functions.
SUM Function
Step 6: Now, let's use the INDIRECT function with the SUM function. In cell D1, enter the following formula=SUM(INDIRECT("A1:A3"))
Step 7: Press Enter. Cell D1 should now display the sum of the values in cells A1 to A3, which is 30 (05 + 10 + 15).
AVERAGE Function
Step 8: Next, use the INDIRECT function with the AVERAGE function. In cell E1, enter the following formula=AVERAGE(INDIRECT("A1:A3"))
Step 9: Press Enter. Cell E1 will display the average values in cells A1 to A3, which is 10 (total sum = 30 divided by 3).
MAX Function
Step 10: Let's use the INDIRECT function with the MAX function. In cell F1, enter the following formula=MAX(INDIRECT("A1:A3"))
Step 11: Press Enter. Cell F1 should now display the maximum value among the cells A1 to A3, which is 15.
Step 12: Last but not least, you can further test the dynamic nature of the INDIRECT function by changing the value in cell B1. For example, if you change B1 to 3, cell C1 will now display the value 15 (as it will fetch the value from A3).
Following these steps, we have successfully used the INDIRECT function with different Excel functions to create dynamic cell references and perform calculations based on changing data.
Example 3:How to use the indirect function in different Excel documents
Now, we will guide you to access data in other documents using the INDIRECT function.
Here is the Syntax:
=INDIRECT("[File_Path\Workbook_Name.xlsx]Sheet_Name!Cell_Reference")
Replace the following:
File_Path: The file path of the source Excel document where the data is located. If the file is in the same folder as the destination workbook, you can just use the filename without the path.
Workbook_Name.xlsx: The name of the source Excel workbook that contains the data you want to reference.
Sheet_Name: The sheet's name in the source workbook where the data is located. If the data is on the same sheet where the formula is located, you can use the sheet name followed by an exclamation mark (!).
Cell_Reference: The cell or range reference you want to access in the source workbook. Use the appropriate cell or range reference relative to the source workbook.
Step-by-step tutorial on how to use the INDIRECT function to reference cells in different Excel documents:
Step 1: Open the Excel workbook where you want to create the formula that references cells from another Excel document. Let’s create a sheet with the name of summary as given below :
Step 2: And also, add 2 more sheets with the names ‘Store1’ & ‘Store2’
Step 3: Add some sample data in Store1 and Store2 Sheets.
Step 4: Now, open a new Excel document that will be used to demonstrate the implementation of the indirect function in different Excel documents.
We have renamed this workbook and its sheet with ‘Store3’ and also created sample data, as mentioned in the above Screenshot.
Step 5: Now, we will refer to sheets within the same workbook and among different Excel documents via the Indirect function.
Formula =SUM(INDIRECT(A2&”!B2:B5”))
With the help of the above formula, we are going to calculate the total number of sales in the Store 1 Sheet.
Step 7: Copy and paste the above-mentioned formula in Cell B2 of the Summary sheet. We will get the following result: 370
Step 8: Similarly, we will add the Total Earnings of Store 2 in a different Excel Sheet i.e.; “Summary”.
Step 9: In our last step, we will get access to the data from another Excel document we have already created with the name of ‘Store 3’.
Step 10: Formula =INDIRECT("[Store3.xlsx]Store3!B6")
After applying this formula in Cell B4 of the Summary Sheet, we can successfully add the total number of sales from Source Sheet (Store3) sheet to Destination Sheet (Summary) as mentioned in given below Screenshot :
Note: In case of #REF! Error, you have to open the Excel document ‘Store3’ in the background. Moreover, place these different Excel documents in the same Folder; otherwise, you have to specify the file path in the formula.
By following these steps, we can easily use the INDIRECT function and work with data from multiple sources to keep your workbooks interconnected and dynamic.
As we have learned the INDIRECT function from basic to advance level. This capability can be extremely useful during working on databases, where frequent changes occur, and enable you to synchronize these changes in your data.
Part 3. Best Alternative to Microsoft Office - WPS Office
1. What is WPS Office
WPS Office is a multi-platform office suite software developed by Kingsoft. It includes word processing, spreadsheet, and presentation applications, providing an alternative to Microsoft Office.
Some users prefer WPS Office due to its user-friendly interface, cost-effectiveness (it offers a free version with many features), and cross-platform compatibility.
2. How to Use Indirect Function in WPS Office
A step-by-step guide with the help of Excel indirect function example in WPS Spreadsheet :
Step 1: Open WPS Spreadsheets and create a new spreadsheet or open an existing one where you want to use the INDIRECT function.
Step 2: Enter some data into the cells. For this tutorial, let's use a simple example with the following data in cells A1 to A3:
A1: 30
A2: 40
A3: 50
Step 3: In a different cell, say cell B1, enter the number 2. This will be our row number for the INDIRECT function.
Step 4: In cell C1, enter the following formula=INDIRECT("A" & B1)
Step 5: Press Enter. Cell C1 should now display the value 40 because the INDIRECT function combines the text "A" with the value in cell B1 (which is 2) to create the reference "A2" and fetches the value from cell A2.
Step 6: Now, let's make it more dynamic. In cell D1, enter the number 3. This will act as our row number for the INDIRECT function.
Step 7: In cell E1, enter the following formula=INDIRECT("A" & D1)
Step 8: Press Enter. Cell E1 should now display the value 50 because the INDIRECT function combines the text "A" with the value in cell D1 (which is 3) to create the reference "A3" and fetches the value from cell A3.
By following these steps, you've successfully used the INDIRECT function in WPS Spreadsheets to create dynamic cell references and retrieve data from specific cells based on the values in other cells.
FAQs
1. What is the difference between direct and indirect functions?
The term "DIRECT" function is not standard in Excel or WPS Office. It does not have a defined purpose. However, the "INDIRECT" function is a built-in function in Excel and WPS Spreadsheets, allowing users to create dynamic cell references indirectly by interpreting text strings as cell references.
2. What is the alternative to indirect in Excel?
An alternative to the Excel INDIRECT function in Excel is using direct cell references in formulas by constructing dynamic cell references based on text strings. You can directly reference cells by specifying their addresses in formulas.
3. Why isn't my indirect function working?
There could be several reasons why your INDIRECT function is not working in Excel. Here are some common issues and troubleshooting steps to resolve the problem:
Incorrect Syntax
Missing Quotation Marks
Invalid Cell Reference
File Path Issues
Volatile Function Recalculation
Circular Reference and
Disabled Formulas.
By reviewing and addressing these potential issues, you would be able to identify why your INDIRECT function is not working in Excel and find a solution to make it function correctly.
4. Are there any limitations to using indirect functions in Excel?
Yes, there are limitations to using INDIRECT functions in the following ways:
External References: INDIRECT can't refer to closed workbooks unless they are open or managed with third-party tools.
Range Complexity: It works best with single-cell references or continuous ranges, limiting its use for non-contiguous or complex ranges.
Error Handling: INDIRECT doesn't handle errors effectively, returning the same error if the referenced cell or range contains one, making debugging challenging.
5. Can I use the Excel indirect function to reference cells from another worksheet or workbook?
You can use the INDIRECT function to reference cells from another worksheet or workbook. It allows you to create dynamic cell references indirectly by using text strings to specify the sheet and cell references. Ensure the referenced worksheet or workbook is open for INDIRECT to work correctly, and consider the limitations and performance impact when using it with external references.
Summary
Last but not least, the Excel INDIRECT function is a powerful tool that enables users to create dynamic and flexible formulas by referencing cells, as you have learned in our step-by-step tutorials. You can now consolidate data to enhance efficiency in custom reports and dashboards.
WPS Excel is the best alternative to Microsoft Excel, which offers a free version of a user-friendly interface with cross-platform compatibility. Due to its feature-rich, cost-effective, and user-friendly office suite, it caters to the needs of individuals, students, professionals, and businesses alike.
