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
