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:

`=INDIRECT(ref_text, [a1])`

**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.

`=SUM(INDIRECT($H$4))`

- Similarly, Select
**H6, H7, and H8**cells accordingly and insert the below formula respectively

`=AVERAGE(INDIRECT($H$4))`

`=MAX(INDIRECT($H$4))`

`=MIN(INDIRECT($H$4))`

**Short Explanation of Formula:**

**=SUM(INDIRECT($H$4))**

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.

**Note:**Use of the dollar sign ($) in the reference to cell

**H4**means that the reference is absolute and will not change if the formula is copied to another cell.

**Read More: **How to Use Named Range in Excel VLOOKUP Function

### 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.

**Note:**Use of the dollar sign ($) in the reference to cell

**C4**means that the reference is absolute and will not change if the formula is copied to another cell.

**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

`=INDIRECT(G5,FALSE)`

- 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**. This is the reason why Excel users rarely consider using R1C1 as an alternative referencing approach.*R1C1 check box* - 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.

## Conclusion

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.

## Related Articles

- How to Create Dynamic Named Range in Excel
- How to Use Dynamic Named Range in an Excel Chart
- How to Ignore Blank Cells in Named Range in Excel
- Define Names with the Create from Selection Tool in Excel

**<< Go Back to Named Range | Excel Formulas | Learn Excel**