Unfortunately, there is no simple way to do this with a formula in Excel.
Step 1: Define the name
The first step is to define a new name.
Navigate to the “Formulas” tab and smack the “Define Name” button.
In the “Name” field, enter “sheetnames”.
This is essentially a hack that still works in later versions of Excel.
The purpose of this cryptic code is to trigger a recalculation when the worksheet changes.
If you save the file as a normal worksheet (.xlsx), the sheetname code will be removed.
The TRANSPOSE function converts the originalhorizontal arrayinto avertical array.
The individual sheet names land in cell B5 andspillonto the worksheet.
If you are using an older version of Excel without TEXTAFTER, see the next section.
The result is a number that indicates the position of this bracket.
We add 1 because we want to start extracting the sheet nameafterthe closing bracket.
The value fornum_charsis hardcoded as 31 to keep things simple.
Whennum_charsexceeds the length of the text following the start number, MID returnsall remaining text.
We do this with the INDEX function.
The array is delivered to INDEX as thearrayargument.
This allows INDEX to retrieve the next sheet name from the array at each new row.
Copy the formula down until all sheet names are listed.
When there are no more sheet names to output, the formula will return a #REF error.
To reset the blocked error, you will need to enable this setting.
Check the checkbox and reopen the file.
This time, when you are prompted to enable VBA macros the #BLOCKED error should be cleared.
Below is a summary of the steps to list all Sheet names with Power Query.
To refresh results, first save the workbook.
Then Right-nudge the table and select “Refresh”.
On the Mac, Power Query is available in Excel 365 but the feature set is more limited.
The example above does work in a current version of Excel 365 on a Mac.
it’s possible for you to use INDEX to retrieve individual values, or entire rows and columns.
For example, =MID(“apple”,2,3) returns “ppl”.
ROW Function
The Excel ROW function returns the row number for a reference.
For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet.
When no reference is provided, ROW returns the row number of the cell which contains the formula.
The NOW function takes no arguments.
it’s possible for you to use the T function to remove values that are not text.
