Indirect named range different sheet

Generic formula

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

Related formulas

Worksheet name exists

Dynamic worksheet reference

3D sum multiple worksheets

3D SUMIF for multiple worksheets

Explanation

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.

How this formula works

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.