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 COLUMN Function
  • How to get relative row numbers in a range in Excel
  • How to use Excel MMULT Function
  • Count unique text values with criteria
  • Create hyperlink with VLOOKUP in Excel

Data Analysis Examples

  • How to perform a t-Test in Excel
  • Excel Frequency Function Example
  • How to Create Column Chart in Excel
  • Conditional Formatting Rules in Excel
  • How to sum a total in multiple Excel tables

Data Validation Examples

  • Excel Data validation unique values only
  • Excel Data validation must not contain
  • Excel Data validation must begin with
  • Prevent invalid data entering in specific cells
  • Excel Data validation number multiple 100

How to create dynamic worksheet reference in Excel

by

To create a formula with a dynamic sheet name you can use the INDIRECT function.

Note: The point of this approach is it lets you to build a formula where the sheet name is a dynamic variable. So, for example, you could change a sheet name (perhaps with a drop down menu) and pull in information from different worksheet.

Worked Example:   How to reference named range different sheet in Excel

Formula

=INDIRECT(sheet_name&"!A1")

Explanation

In the example shown, the formula in C6 is:

=INDIRECT(B6&"!A1")

How this formula works

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 concatenation, we have:

=INDIRECT("Sheet1!A1")

INDIRECT recognizes this as a valid reference to cell A1 in Sheet1, and returns the value in A1 (1000).

Worked Example:   Steps To Password Protect a Workbook in Excel

In cell C7, the formula evaluates like this:

=INDIRECT(B7&"!A1")
=INDIRECT("Sheet2!A1")
=200

And so on, for each formula in column C.

Worked Example:   Average the last 3 numeric values in Excel

Handling spaces and punctuation in sheet names

If sheet names contain spaces, or punctuation characters, you’ll need to adjust the formula to wrap the sheet name in single quotes like this:

=INDIRECT("'"&sheet_name&"'!A1")

where sheet_name is a cell address like B6 in the example shown.

Post navigation

Previous Post:

Create dynamic workbook reference to another workbook in Excel

Next Post:

How to get sheet name only 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
  • SUBSTITUTE function vs REPLACE function in Excel
  • How to count line breaks in cell in Excel worksheet
  • How to find nth occurrence of character in Excel
  • How to extract text between parentheses in Excel
  • How to check if cell contains some words but not others in Excel
  • Get first day of month in Excel
  • Add business days to date in Excel
  • Calculate series of dates by workdays in Excel
  • Calculate retirement date in Excel
  • YEAR function: Description, Usage, Syntax, Examples and Explanation
  • DOLLARDE function: Description, Usage, Syntax, Examples and Explanation
  • YIELDMAT function: Description, Usage, Syntax, Examples and Explanation
  • PRICE function: Description, Usage, Syntax, Examples and Explanation
  • Calculate cumulative loan principal payments in Excel
  • XNPV function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles