Summary

To reference a named range on another sheet, you can use the INDIRECT function with the required sheet syntax. In the example shown, the formula in D6 is:

=SUM(INDIRECT("'"&B6&"'!"&C6))

Which returns the sum of the named range "data" on Sheet1.

Generic formula

INDIRECT("'"&sheet&"'!"&name)

Explanation 

The formula above evaluates something like this:

=SUM(INDIRECT("'"&B6&"'!"&C6))
=SUM(INDIRECT("'"&"Sheet1"&"'!"&"data"))
=SUM('Sheet1'!data)

Once the string is assembled using values in B6 and C6, INDIRECT evaluates and transforms the string into a proper reference.

Note you can refer to a named range in a formula without using INDIRECT. For example, the formula in D6 could be written:

=SUM('Sheet1'!data)

However, if you want to assemble the reference as text, and have Excel treat the text as a reference, you need to use INDIRECT.

Note: The single quotes are added in the formula above so that the formula will work when a sheet name contains spaces.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.