Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel TRANSPOSE Function
  • Extract data with helper column in Excel
  • How to get relative column numbers in a range in Excel
  • How to get first column number in range in Excel
  • How to get last row in text data in Excel

Data Analysis Examples

  • Conditional Formatting Data bars Examples in Excel
  • How to combine 2 or more chart types in a single chart in Excel
  • How to create a Histogram in Excel
  • How to Use Solver Tool in Excel
  • How To Remove Duplicates In Excel Column Or Row?

Data Validation Examples

  • Excel Data validation must not contain
  • Excel Data validation require unique number
  • Excel Data validation exists in list
  • Excel Data validation whole percentage only
  • Excel Data validation only dates between

How to reference named range different sheet in Excel

by

This tutorials shows how to reference a named range on another sheet.

To achieve this, you can use the INDIRECT function with the required sheet syntax.

Formula

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

Explanation

In the example shown, the formula in D6 is:

=SUM(INDIRECT("'"&B6&"'!"&C6))

Which returns the sum of the named range “data” on Sheet1.

Worked Example:   How to strip non-numeric characters in Excel

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.

Worked Example:   Embed Excel worksheet in Word document

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)

Note: The single quotes are added in the formula above so that the formula will work when a sheet name contains spaces.

However, if you want to assemble the reference as text, and have Excel treat the text as a reference, you need to use INDIRECT.

Worked Example:   Sum bottom n values with criteria in Excel

 

Post navigation

Previous Post:

How to get Excel workbook path only

Next Post:

List sheet names with formula in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to extract name from email address in Excel
  • T function: Description, Usage, Syntax, Examples and Explanation
  • How to check cell contains which things in Excel
  • How to count specific words in a cell in Excel
  • Extract most frequently occurring text in Excel
  • Get days, months, and years between dates in Excel
  • ISOWEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Get month name from date in Excel
  • Get date from day number in Excel
  • Convert date to month and year in Excel
  • Future value vs. Present value examples in Excel
  • PMT, RATE, NPER, PV and FV Financial Functions in Excel
  • Calculate payment for a loan in Excel
  • XNPV function: Description, Usage, Syntax, Examples and Explanation
  • Bond valuation example in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles