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
  • Two-column Lookup in Excel
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to get address of named range in Excel
  • How to use Excel ROWS Function

Data Analysis Examples

  • How to combine 2 or more chart types in a single chart in Excel
  • How To Create Frequency Distribution in Excel
  • Managing Conditional Formatting Rules in Excel
  • How to Sort by Color in Excel
  • How to Create Column Chart in Excel

Data Validation Examples

  • Excel Data validation no punctuation
  • Excel Data validation require unique number
  • Excel Data validation must contain specific text
  • Data validation must not exist in list
  • Excel Data validation allow uppercase only

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:

Worked Example:   Sum bottom n values in Excel
=SUM(INDIRECT("'"&B6&"'!"&C6))

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

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:   Count Unique Values in Excel

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:   Freeze and Unfreeze Panes 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
  • TEXTJOIN function: Description, Usage, Syntax, Examples and Explanation
  • How to count specific characters in a cell in Excel
  • How to count number of characters of text in a cell in Excel
  • Extract last name from full name — Manipulating NAMES in Excel
  • Split dimensions into two parts in Excel Worksheet
  • Get days before a date in Excel
  • How to calculate next day of week in Excel
  • Get day name from date in Excel
  • How to get workdays between dates in Excel
  • TODAY function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate Net Present Value (NPV) in Excel
  • How to calculate annual compound interest schedule in Excel
  • Calculate original loan amount in Excel
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
  • DURATION function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles