Excel formula: Indirect named range different sheet

Generic formula

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

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.

Author

Dave Bruns

Related formulas

Worksheet name exists

To test if a worksheet name exists in a workbook, you can use a formula based on the ISREF and INDIRECT functions. In the example shown, the formula in C5 is: = ISREF ( INDIRECT ( B5 & "!A1" )) How this formula works The ISREF function...

Dynamic worksheet reference

To create a formula with a dynamic sheet name you can use the INDIRECT function. In the example shown, the formula in C6 is: = INDIRECT ( B6 & "!A1" ) Note: The point of this approach is it lets you to build a formula where the sheet...

3D sum multiple worksheets

To sum the same range in one or more sheets, you can use the SUM formula with a special syntax called a "3d reference". In the example shown, the formula in D6 is: = SUM ( Week1:Week5 ! D6 ) How this formula works The syntax for referencing a range...

3D SUMIF for multiple worksheets

To conditionally sum identical ranges that exist in separate worksheets, all in one formula, you can do so with the SUMIF function + INDIRECT, wrapped in SUMPRODUCT. In the example, the formula looks like this: = SUMPRODUCT ( SUMIF ( INDIRECT (...

Related functions

Excel INDIRECT Function

The Excel INDIRECT function returns a valid reference from a given text string. Use INDIRECT when you need to convert a reference assembled as text into a proper reference.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.