The article provides an in-depth explanation of how to use the INDIRECT function with Named Range in Microsoft Excel. The INDIRECT function accepts a string representing a cell reference, evaluates the reference, and displays the contents of the cell. This gives you a better way to deal with variable cell references. Named Ranges are a powerful feature in Excel that allows users to assign a name to a range of cells, making it easier to reference and use the range throughout a workbook. However, working with Named Ranges can sometimes be challenging, especially when trying to reference them in a formula dynamically.
This is where the INDIRECT function comes in handy. Using the INDIRECT function, users can refer to a Named Range indirectly and create dynamic formulas that automatically update when the Named Range is modified. The article explains how to use the INDIRECT function with Named Ranges step-by-step. We will also discuss how to use this function in multiple worksheets.
Introduction to INDIRECT Function in Excel
Syntax of INDIRECT Function:
The INDIRECT function in Excel references a range based on a text string. The syntax of the INDIRECT function is as follows:
ref_text (required): A cell reference that points to a cell that includes a text string. A cell reference, a named range, or a formula that yields a valid cell reference can all be used as the text string.
[a1] (optional): A logical value that specifies the type of reference to use. If set to TRUE or omitted, the function uses an A1-style reference. If set to FALSE, the function uses R1C1-style reference.
While the R1C1 reference type may be advantageous occasionally, you should probably stick to the well-known A1 references the majority of the time. In any case, A1 references will be used in almost all INDIRECT formulas in this lesson, therefore we will skip the second argument.
INDIRECT Function with Named Range in Excel: 2 Suitable Examples
Let’s get introduced to our dataset. This image consists of data on Sales Representatives and the price of Fruits, Vegetables, and Meat in columns respectively B, C, D, and E.
We are taking the following Named Ranges in our worksheet:
- Fruits- C5:C10
- Vegetables- D5:D10
- Meat- E5:E10
1. Applying INDIRECT Function with Named Range for Single Worksheet in Excel
The INDIRECT function can assist in simplifying your formulas and making them simpler to understand and manage when used in combination with named ranges, which are user-defined names assigned to a cell or range of cells. By dynamically changing the referred cells or range of cells, the named ranges save the time required to update and modify your calculations. We will use the SUM, AVERAGE, MAX, and MIN functions along with the INDIRECT function to utilize the Named Range more effectively.
First, select cell H5 and input the following formula.
- Similarly, Select H6, H7, and H8 cells accordingly and insert the below formula respectively
Short Explanation of Formula:
Here, the INDIRECT function refers to a cell or range of cells indirectly. The $H$4 reference in the formula represents a cell address where a named range is stored as text. The INDIRECT function takes this text string as its argument and returns a reference to the cell or range of cells named in that text string.
The returned reference is then passed as an argument to the SUM function, which calculates the sum of the values in the referenced cells.
2. Using INDIRECT Function with Named Range for Different Worksheets
The INDIRECT function in Microsoft Excel is a helpful tool for referring to cell ranges that are spread across various worksheets within the same workbook. The INDIRECT function, when used in combination with named ranges, can greatly simplify the management and maintenance of big spreadsheets including several sheets and enormous amounts of data. This lesson will show you how to link to cell ranges that are located in various worksheets using the INDIRECT function and named ranges, and simplifying your spreadsheet work.
Insert the formula mentioned below in cell H5.
=INDIRECT("'" & $C$4 & "'!" & C5)
- We will get 29 as output which is the value from Single Sheet’s cell E5
Short Explanation of Formula:
The formula =INDIRECT(“‘” & $C$4 & “‘!” & C5) is used to reference a cell in another sheet in the same workbook based on the values in cells C4 and C5.
& is the concatenation operator that combines the text strings and cell references.
Read More: Excel Reference Named Range in Another Sheet
How to Fix When Excel INDIRECT Function with Named Range Is Not Working
If you see the image above, you can notice that some of the cells show #REF Error.
Particularly in cells H5 to H8.
It’s because in cell H4 we took an inappropriate Name Range. “Food” doesn’t exist as Name Range in this sheet. That’s why H5 to H8 cells show #REF Error.
Now, We took “Meat” as the Name Range in cell H4 which is the correct Name Range if you notice the picture above.
Thus, the image displays no error this time due to taking the correct Name Range.
How to Use INDIRECT Formula with R1C1 References in Excel
R1C1 is the opposite reference type: rows followed by columns, which takes some getting used to. For instance, cell A7 on a sheet’s row 7, column 1, is referred to as R7C1. When a letter is followed by a number, you are referring to the same row or column.
Let’s now examine how the INDIRECT function manages references to R1C1:
First, select cell H5 and apply the formula written below
- You’ll see Stephen as output.
Short Explanation of the Formula:
In this formula, =INDIRECT(G5,FALSE), G5 indicates the reference, and the second parameter “FALSE” indicates that the reference is in R1C1 format. On the G5 cell, the value is R7C2, which means that it is pointing to row 7 and column 2. And the value at row 7 and column 2 is Stephen.
Read More: How to Display Named Range Contents in Excel
Things to Remember
- If the INDIRECT formula refers to another worksheet, that workbook must be open or else the formula will yield a #REF!
- The Excel INDIRECT function allows for A1 and R1C1 reference styles. Normally, you can’t utilize both styles simultaneously on a single sheet. You can only switch between the two reference types via the File > Options > Formulas > R1C1 check box. This is the reason why Excel users rarely consider using R1C1 as an alternative referencing approach.
- INDIRECT is a volatile function. This indicates that it performs a new calculation each time the Excel workbook is opened or a worksheet calculation is initiated. This lengthens the processing time and makes your workbook slower. While the indirect method can be used with small datasets with little to no influence on speed, using it with large datasets may result in your worksheet running more slowly.
Frequently Asked Questions
- What will happen if the referred sheet or workbook is closed?
The other Excel workbook or spreadsheet must be open if your Indirect formula references it; otherwise, INDIRECT will return the #REF! error.
- Can you use indirect for a range?
Excel INDIRECT is used to indirectly reference cells, ranges, other sheets, or workbooks, as its name implies. In other words, rather than hard-coding cell or range references, you can build them dynamically using the INDIRECT function.
Download Practice Workbook
If you want a free copy of the illustrated workbook we discussed during the presentation, please click the link below this section.
The INDIRECT function with named ranges can be a strong Excel tool for referencing data across numerous worksheets, to sum up. This function makes it simple to reference a named range in a formula or function, even if it’s on a different worksheet. We’ve looked at two approaches—one for a single worksheet and the other for several worksheets—for using Excel’s INDIRECT function with named ranges. You may automate your Excel workflows using these methods, and your spreadsheets will become more dynamic and adaptable.
Please feel free to ask us questions in the comments section below if you have any tips, thoughts, or concerns about this topic.