This guide covers everything you need to know about the Google Sheets INDIRECT function, including its definition, syntax, use cases, and how to use it.
What is the INDIRECT Function? How Does It Work?
The INDIRECT function in Google Sheets is a powerful function that returns a cell reference specified by a string. This means that it treats whatever you put into it as a string, but then interprets that string as a cell reference. So instead of directly linking to a cell, which would then return the content of that cell, you provide the INDIRECT function with the cell reference in text form.
The primary purpose of the INDIRECT function is to provide a dynamic way to reference a cell or a range of cells. This can be incredibly useful in scenarios where the cell you want to reference might change, but you always know how to find it based on other information in your spreadsheet.
For instance, let’s look at the example of INDIRECT(“Sheet2!”&B10). In this instance, the INDIRECT function is combining the text “Sheet2!” with the value in cell B10 to create a reference to a cell in Sheet2. The ‘!’ is used to separate the sheet name from the cell reference. So, if B10 contained the value “A1”, the INDIRECT function would be looking at cell A1 on Sheet2.
In the example INDIRECT(“R2C3”, FALSE), the FALSE indicates that the cell reference is not in A1 notation but in R1C1 notation. R2C3 means Row 2 Column 3, which in A1 notation would be cell C2.
It’s important to note that the INDIRECT function does not merely return the string of the cell reference, but rather it returns the contents of that cell reference. This enables you to use the cell reference within further calculations or functions within your Google Sheets, providing a highly flexible and dynamic way to manage your data.
The INDIRECT function is a clever way to navigate complex spreadsheets, allowing for dynamically shifting references based on the content of other cells. It offers incredible versatility for data manipulation, making your spreadsheets more responsive and adaptable.
INDIRECT Syntax
The syntax and arguments for the function are as follows:
INDIRECT(cell_reference_as_string, [is_A1_notation])
- cell_reference_as_string: This is the primary argument of the INDIRECT function. It represents a cell reference, which must be written as a string with surrounding quotation marks. For instance, if you are referring to cell A1, you should input it as “A1” in the function.
- is_A1_notation: This is an optional argument, which is set to TRUE by default. It specifies the type of notation used for the cell reference. If it’s TRUE, the function expects the A1 notation (letters representing columns and numbers representing rows, such as “A1”). If it’s FALSE, the function expects the R1C1 notation (both columns and rows are represented by numbers, such as “R1C1”).
Usage notes related to syntax and arguments:
- The INDIRECT function in Google Sheets does not directly evaluate cell references. Instead, it interprets the cell reference as a string. This makes it particularly useful for dynamic referencing.
- The cell_reference_as_string argument should always be in quotation marks to be recognized as a string. If you want to use a cell reference that is stored in another cell, you can concatenate it with an empty string or use the TEXT function to convert it to a string.
- The is_A1_notation argument is optional and defaults to TRUE. This means that if you don’t specify this argument, Google Sheets will assume that you’re using A1 notation. If you want to use R1C1 notation, you need to set this argument to FALSE.
- Make sure the cell reference provided as an argument is valid. If the INDIRECT function can’t find the cell you’re referring to (for example, if you give a reference outside the existing range of your sheet), it will return a #REF! error.
- The INDIRECT function is volatile, which means it recalculates every time a change is made in the sheet, even if it doesn’t directly affect the cell with the INDIRECT function. This could potentially slow down your Google Sheets performance if used excessively.
- The INDIRECT function does not work with references to other Google Sheets or Excel files. It only works with references within the same Google Sheets file.
Examples of How to Use the INDIRECT Function
Here are some practical examples of how to use the INDIRECT function in Google Sheets:
Example #1: Reference a Single Cell
Suppose you have a value in cell A1, and you want to reference this cell in another cell by using the INDIRECT function. In cell B1, you can type the formula =INDIRECT(“A1”). This formula will return the value in cell A1.
Example #2: Referencing a Range of Cells
Suppose you have a range of values in cells A1 to A5, and you want to sum these values using the INDIRECT function. In cell B1, you can type the formula =SUM(INDIRECT(“A1:A5”)). This formula will return the sum of the values in the range A1 to A5.
Example #3: Dynamic Cell Reference
The INDIRECT function can also be used for dynamic cell referencing. For instance, if you have a value in cell A1 and you want to reference this cell in another cell, but the cell reference (A1) is in cell B1. In cell C1, you can type the formula =INDIRECT(B1). This formula will return the value in cell A1.
Example #4: Referencing Cells in Different Sheets
The INDIRECT function can also reference cells in different sheets. Suppose you have a value in cell A1 of Sheet2, and you want to reference this cell in Sheet1. In cell B1 of Sheet1, you can type the formula =INDIRECT(“Sheet2!A1”). This formula will return the value in cell A1 of Sheet2.
INDIRECT: Common Mistakes & Problems
When using the INDIRECT function in Google Sheets, there are several common mistakes and problems that users may encounter:
- Using incorrect cell references: One of the most common mistakes is using incorrect cell references. The INDIRECT function will not work if the cell reference is not valid. For example, if you try to reference a cell that does not exist, such as “Z1000” in a default Google Sheet, the function will return a #REF! error.
- Forgetting to use quotation marks: When using the INDIRECT function with a string argument, you need to enclose the string in quotation marks. For example, INDIRECT(“A1”) is correct, but INDIRECT(A1) without quotation marks will result in an error unless A1 contains a valid cell reference.
- Not understanding the difference between relative and absolute references: When the second argument of the INDIRECT function is TRUE or omitted, the function treats the reference as A1 notation and interprets it relative to the cell in which the formula is entered. When the second argument is FALSE, the function treats the reference as R1C1 notation and interprets it as an absolute reference. Misunderstanding this can lead to incorrect results.
- Ignoring sheet names in cell references: If you’re referencing a cell in a different sheet, you need to include the sheet name in your reference. For example, INDIRECT(“Sheet2!A1”) references cell A1 in Sheet2, but INDIRECT(“A1”) would look for A1 in the current sheet.
- Not considering the impact of moved or deleted cells: If a cell referenced by the INDIRECT function is moved or deleted, the function will return a #REF! error. This is because the INDIRECT function does not automatically update the cell reference when changes are made.
- Using INDIRECT function in large ranges: The INDIRECT function can slow down your Google Sheets performance if it’s used across large ranges, as it forces the sheet to continuously recalculate.
- Incorrect use of named ranges: If you’re using named ranges in your INDIRECT function, ensure that the named range exists and is spelled correctly. Incorrectly named ranges will result in a #REF! error.
Why INDIRECT Is Not Working? Troubleshooting Common Errors
If you’re using the INDIRECT function in Google Sheets and it’s not working as expected, it can be due to some of the common errors covered below:
#VALUE! Error
Cause: This error typically occurs when the INDIRECT function cannot interpret the text string as a cell reference. This could be because the string does not correspond to a valid cell reference or because it includes special characters or spaces that the function cannot interpret.
Solution: Check the text string you’re using in the INDIRECT function. Ensure it corresponds to a valid cell reference and doesn’t include any special characters or spaces. You might need to adjust the string or use other functions to create a valid reference.
#REF! Error
Cause: This error often occurs when the INDIRECT function refers to a cell that doesn’t exist. This could happen if the row or column number is beyond the limits of Google Sheets (which currently supports up to 5 million cells) or if the sheet name specified in the function doesn’t exist.
Solution: Check the cell reference in the INDIRECT function. Ensure the row and column numbers are within the limits of Google Sheets and that the sheet name exists. If you’re referring to a different sheet, make sure the name is spelled correctly and enclosed in single quotes.
Circular Dependency Error
Cause: This error occurs when the INDIRECT function refers to its own cell, creating a circular dependency. Google Sheets can’t calculate the value of a cell when it depends on its own value.
Solution: Check the cell reference in the INDIRECT function. Make sure it doesn’t refer to its own cell or to any cells that depend on its value. You might need to rearrange your formulas or use different cells to avoid creating a circular dependency.
Argument ‘Text’ Is Not a Valid Cell/Range Reference
Cause: This error occurs when the first argument of the INDIRECT function is not a valid cell or range reference. This could be because the argument is missing, not in the correct format, or includes invalid characters.
Solution: Check the first argument of the INDIRECT function. Make sure it’s formatted correctly for a cell or range reference and doesn’t include any invalid characters. Adjust the argument or use other functions to create a valid reference if necessary.
Using INDIRECT With Other Google Sheets Functions
Combining the INDIRECT function with other Google Sheets functions can yield powerful results, allowing you to perform complex data manipulations with ease. The INDIRECT function can be used in tandem with many other functions, including VLOOKUP, SUM, AVERAGE, and COUNT. Here’s how to use INDIRECT with some of these functions:
With VLOOKUP
INDIRECT can be used with the VLOOKUP function to return a value from a specific cell in a different sheet. This is particularly useful when the sheet name is dynamic and changes regularly.
Example: Let’s say you want to fetch data from a cell in a sheet named “Sales Data”. If this sheet name changes every month (e.g., “Sales Data Jan”, “Sales Data Feb”, etc.), you can use INDIRECT with VLOOKUP for this purpose. The formula would look something like this: =VLOOKUP(A2, INDIRECT(“‘”&B1&”‘!A2:B10”), 2, FALSE). In this formula, B1 contains the dynamically changing sheet name.
With SUM
INDIRECT can also be used with the SUM function to add up values in a range of cells across different sheets.
Example: If you have the total sales data for each month in different sheets and want to calculate the total sales for the year, you can use INDIRECT with SUM. The formula would look something like this: =SUM(INDIRECT(“‘Jan’!B2”), INDIRECT(“‘Feb’!B2”), INDIRECT(“‘Mar’!B2”), …, INDIRECT(“‘Dec’!B2”)). This formula sums the values in cell B2 from each monthly sheet.
With AVERAGE
Similarly, INDIRECT can be combined with the AVERAGE function to calculate the average of a range of cells across different sheets.
Example: If you want to calculate the average sales over a quarter, you could use a formula like this: =AVERAGE(INDIRECT(“‘Jan’!B2:B31”), INDIRECT(“‘Feb’!B2:B29”), INDIRECT(“‘Mar’!B2:B31”)). This formula averages the values in cells B2 through B31 (or B29 for February) from each monthly sheet.
With COUNT
Finally, INDIRECT can be used with the COUNT function to count the number of cells in a range that contains numbers across different sheets.
Example: If you want to count the number of days in a month that had sales, you could use a formula like this: `=COUNT(INDIRECT(“‘Jan’!B2:B31”))`. This formula counts the number of cells in the range B2 through B31 in the ‘Jan’ sheet that contains numbers.
For more details on the INDIRECT function, check out the official documentation at the Google Docs Editors Help Center.
