Excel formula: Indirect named range different sheet

Generic formula

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

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.

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.

Author

Dave Bruns

Related formulas

Worksheet name exists

The ISREF function returns TRUE for a valid worksheet reference and FALSE is not. In this case, we want to find out of a particular sheet exists in a workbook, so we construct a full reference by concatenating the sheet names in column B with an...

Dynamic worksheet reference

The INDIRECT function tries to evaluate text as a worksheet reference. In this example, we have Sheet names in column B, so we join the sheet name to the cell reference A1 using concatenation: = INDIRECT ( B6 & "!A1" ) After...

3D sum multiple worksheets

The syntax for referencing a range of sheets is a built-in feature and works a bit like a reference to a range of cells. For example Sheet1:Sheet3 ! A1 Means: cell A1 from Sheet1 to Sheet3. In the example shown: = SUM ( Week1:Week5 ! D6 ) Will sum...

3D SUMIF for multiple worksheets

The data on each of the three sheets being processed looks like this: First of all, note that you can't use SUMIFs with a " normal " 3D reference like this: Sheet1:Sheet3 ! D4:D5 This is the standard "3D syntax" but if you try to use it with SUMIF,...

Related functions

Excel INDIRECT Function

The Excel INDIRECT function returns a valid reference from a given text string. INDIRECT is useful when you want to convert a text value into a valid cell 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.