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

Lookup and Reference Examples

  • Extract all partial matches in Excel
  • Get nth match with INDEX / MATCH in Excel
  • How to get first row number in range in Excel
  • How to get address of first cell in range in Excel
  • Complete List of Excel Lookup and Reference Functions, References and Examples

Data Analysis Examples

  • Working With Tables in Excel
  • How to create dynamic reference table name in Excel
  • Conditional Formatting Color Scales Examples in Excel
  • How to create Gauge Chart in Excel
  • Conflicting Multiple Conditional Formatting Rules in Excel

Data Validation Examples

  • Excel Data validation number multiple 100
  • Excel Data validation must contain specific text
  • Excel Data validation specific characters only
  • Excel Data validation must not contain
  • Excel Data validation don’t exceed total

List sheet names with formula in Excel

by

To list worksheets in an Excel workbook, you can use a 2-step approach: (1) define a named range called “sheetnames” with an old macro command and (2) use an INDEX formula to retrieve sheet names using the named range.

Formula

=GET.WORKBOOK(1)&T(NOW())

Note: because this formula relies on a macro command, you’ll need to save as a macro-enabled workbook if you want the formula to continue to update sheet names after the file is closed and re-opened. If you save as a normal worksheet, the sheetname code will be stripped.

Worked Example:   How to create running total in an Excel Table

Explanation

In the example shown, the formula in B5 is:

=INDEX(MID(sheetnames,FIND("]",sheetnames)+1,255),ROWS($B$5:B5))

How this formula works

The named range “sheetnames” is created with this code:

=GET.WORKBOOK(1)&T(NOW())

GET.WORKBOOK is a macro command that retrieves an array of sheet names in the current workbook. The resulting array looks like this:

{"[workbook.xlsm]Sheet1","[workbook.xlsm]Sheet2","[workbook.xlsm]Sheet3",
"[workbook.xlsm]Sheet4","[workbook.xlsm]Sheet5"}

A cryptic expression is concatenated to the result:

&T(NOW())

The purpose of this code is to force recalculation to pick up changes to sheet names. Because NOW is a volatile function, it recalculates with every worksheet change. The NOW function returns a numeric value representing date and time. The T function returns an empty string (“”) for numeric values, so the concatenation has no effect on values.

Worked Example:   Lookup entire row in Excel

Back on the worksheet, cell B6 contains this formula copied down:

=INDEX(MID(sheetnames,FIND("]",sheetnames)+1,255),ROWS($B$5:B5))

Working from the inside out, the MID function is used to remove the worksheet names. The resulting array looks like this:

{"Sheet1","Sheet2","Sheet3","Sheet4","Sheet5"}

This goes into the INDEX function as “array”. The ROW function uses an an expanding ranges to generate an incrementing row number. At each new row, INDEX returns the next array value. When there are no more sheet names to output, the formula will return a #REF error.

Worked Example:   Prevent invalid data entering in specific cells

Post navigation

Previous Post:

How to reference named range different sheet in Excel

Next Post:

List worksheet index numbers 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 use double quotes inside a formula in Excel
  • UNICHAR function: Description, Usage, Syntax, Examples and Explanation
  • Manipulating text strings using Left, Mid, Right, Len, Substitute in Excel
  • How to strip numeric characters from cell in Excel
  • Split dimensions into two parts in Excel Worksheet
  • Basic Overtime Calculation Formula in Excel
  • Roll back weekday to Friday base on a particular date in Excel
  • Convert Excel time to decimal seconds
  • EOMONTH function: Description, Usage, Syntax, Examples and Explanation
  • Add days exclude certain days of week in Excel
  • How to set check register balance in Excel
  • Calculate interest for given period in Excel
  • TBILLPRICE function: Description, Usage, Syntax, Examples and Explanation
  • DB function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate compound interest in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles