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.
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 exclamation mark and "A1": B5...
Dynamic worksheet reference
The INDIRECT function tries to evaluate text as a worksheet reference. This makes it possible to build formulas that assemble a reference as text using concatenation , and use the resulting text as a valid reference. In this example, we have Sheet names in column B, so we join the sheet name to the...
Sum across multiple worksheets
In this example, the goal is to sum total points for each student across five worksheets that all have the same structure. This can be accomplished with a 3D reference, as explained below. Standard reference Before we look at how 3D references work, let's look at the standard approach. To solve...
Sum across multiple worksheets with criteria
In this example, the goal is to sum hours per project across three different worksheets: Sheet1, Sheet2, and Sheet3. The data on each of the three sheets has the same structure as Sheet1, as seen below: 3D reference won't work Before we look at a solution, let's look at something that doesn't work...
Related functions
INDIRECT Function
The Excel INDIRECT function returns a valid cell reference from a given text string. INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.
