Note: I ran into this formula on the MrExcel message board in a post by T. Valko. GET.WORKBOOK is a macro command that retrieves an array of sheet names in the current workbook. The resulting array looks like this: A cryptic expression is concatenated to the result: 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. Back on the worksheet, cell B6 contains this formula copied down: Working from the inside out, the MID function is used to remove the worksheet names. The resulting array looks like this: 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. 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
