Exceljet

Quick, clean, and to the point

Indirect named range different sheet

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 
Excel formula: Worksheet name exists
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...
Excel formula: Dynamic worksheet reference
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...
Excel formula: 3D sum multiple worksheets
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...
Excel formula: 3D SUMIF for multiple worksheets
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
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.

500 Formulas | 101 Functions

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.