You can use the following basic syntax to reference a named range in another sheet in Excel:

**=SUM(INDIRECT("'"&A2&"'!"&B2))
**

This particular formula will calculate the sum of the values for the named range in cell **B2** within the sheet in cell **A2**.

The following example shows how to use this syntax in practice.

**Example: How to Use INDIRECT with Sheet Names in Excel**

Suppose we have a named range in Excel called **my_data** that is located on a sheet called **Sheet2**:

Suppose we would like to calculate the sum of the values in this named range in **Sheet1**.

We can type the following formula in cell **C2** of **Sheet1** to do so:

**=SUM(INDIRECT("'"&A2&"'!"&B2))**

The following screenshot shows how to use this formula in practice:

The formula returns the sum of the values in the named range called **my_data** in **Sheet2**, which turns out to be **173**.

Note that cell **C2** evaluates to the following expression:

**=SUM('Sheet2'!my_data)
**

Also note that we could use a different function with the **INDIRECT** function if we’d like.

For example, we could type the following formula into cell **C2** of **Sheet1** to instead calculate the average of the values in the named range called **my_data** in **Sheet2**:

**=AVERAGE(INDIRECT("'"&A2&"'!"&B2))**

The following screenshot shows how to use this formula in practice:

The formula returns the average of the values in the named range called **my_data** in **Sheet2**, which turns out to be **17.3**.

**Note**: You can find the complete documentation for the **INDIRECT** function in Excel here.

**Additional Resources**

The following tutorials explain how to perform other common operations in Excel:

How to Compare Two Excel Sheets for Differences

How to Sum Across Multiple Sheets in Excel

How to Calculate the Sum by Group in Excel