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

Lookup and Reference Examples

  • How to create dynamic named range with OFFSET in Excel
  • How to get relative column numbers in a range in Excel
  • How to use Excel ROW Function
  • Count rows that contain specific values in Excel
  • Find closest match in Excel

Data Analysis Examples

  • Number and Text Filters Examples in Excel
  • How To Create Frequency Distribution in Excel
  • How To Compare Two Lists in Excel
  • Get column index in Excel Table
  • How to perform a t-Test in Excel

Data Validation Examples

  • Excel Data validation unique values only
  • Excel Data validation date in specific year
  • Excel Data validation don’t exceed total
  • Excel Data validation exists in list
  • Excel Data validation require unique number

List worksheet index numbers in Excel

by

To list the index numbers of sheets in an Excel workbook, you can enter the sheet names, then use a formula based on the SHEET and INDIRECT functions.

Worked Example:   How to Protect and Unprotect Workbook in Excel

The SHEET function then returns the current index for each sheet as listed.

Formula

=SHEET(INDIRECT(name&"!A1"))

Explanation

In the example shown, the formula in C5 is:

Worked Example:   Group and Ungroup Worksheets in Excel
=SHEET(INDIRECT(B5&"!A1"))

How this formula works

The INDIRECT function tries to evaluate text as a valid reference. In this case, the sheet name is pulled from column B and concatenated with an exclamation point and the text A1:

=B5&"!A1"
="Sheet1"&"!A1"
="Sheet1!A1"

The INDIRECT function then coerces the text “Sheet1!A1” into a valid reference, which is passed into the SHEET function.

Worked Example:   Sum bottom n values in Excel

 

Post navigation

Previous Post:

List sheet names with formula in Excel

Next Post:

Calculate original loan amount 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 convert text string to array in Excel
  • DBCS function: Description, Usage, Syntax, Examples and Explanation
  • How to count specific characters in a cell in Excel
  • How to Separate Text Strings in Excel
  • How to display conditional message with REPT function in Excel
  • Custom weekday abbreviation in Excel
  • HOUR function: Description, Usage, Syntax, Examples and Explanation
  • How to get same date next year or previous year in Excel
  • Convert Excel time to decimal seconds
  • Get work hours between dates and times in Excel
  • FV function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate simple interest in Excel
  • NOMINAL function: Description, Usage, Syntax, Examples and Explanation
  • CUMPRINC function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate compound interest in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles