Indirect named range different sheet

Generic formula
INDIRECT("'"&sheet&"'!"&name)
Summary
Explanation
The formula above evaluates something like this:
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.
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.