Use GET.WORKBOOK to List Worksheet Names without VBA

Summary

In this tutorial, the presenter explores Excel 4.0 functions, focusing on listing worksheet names without using Visual Basic. They introduce the 'GET.WORKBOOK' function, specifically using the number '1' to retrieve worksheet names. The process involves using the Name Manager to create a named formula 'list sheet names'. For Excel 2019 or earlier, an array formula approach is demonstrated. The video also covers how to transpose the list from horizontal to vertical and remove the workbook name from the list using the 'MID' and 'LEN' functions. The presenter encourages viewers to engage with the content and provides a formula for easy reference.

Takeaways

• 📊 **Excel 4.0 Functions**: The video discusses using Excel 4.0 functions to perform tasks that modern Excel functionalities handle with VBA.
• 💾 **Old vs Modern**: Excel 4.0 functions were essential in older versions due to less powerful computers and less intuitive interfaces.
• 🔍 **Get.Workbook Function**: The specific function 'Get.Workbook' is highlighted for listing worksheet names without VBA.
• 📚 **Manual Reference**: A 683-page manual exists detailing Excel 4.0 functions, indicating their complexity and variety.
• 🔢 **Parameter '1'**: The number '1' is used as a parameter with 'Get.Workbook' to retrieve worksheet names.
• 🛠️ **Name Manager**: The Name Manager in Excel is used to create a named reference for the 'Get.Workbook' function for easier use.
• 📝 **Formula Creation**: The process of creating a formula that uses the named reference 'list sheet names' is demonstrated.
• 🔄 **Refreshing Formulas**: It's noted that in older Excel versions, formulas don't automatically update when sheet order changes, requiring manual refresh.
• 📋 **Array Formulas**: For pre-Excel 2019 versions, array formulas are used by entering them in multiple cells and confirming with Ctrl+Shift+Enter.
• {🔀 **Transposing Data**: The video shows how to use the TRANSPOSE function to change data orientation from horizontal to vertical.
• ✂️ **Removing Unwanted Text**: The script explains using the MID and LEN functions to remove the workbook name from the list of worksheet names.

Q & A

What are Excel 4.0 functions?

Excel 4.0 functions are old macro functions that were used in earlier versions of Excel before more modern functionalities like Visual Basic were introduced. Despite being outdated, they can still be used if you know how.

What is the purpose of the 'get.workbook' function in Excel 4.0?

The 'get.workbook' function retrieves specific information about the workbook. In this case, using 'get.workbook(1)' lists the names of all the worksheets in the workbook.

How do you create a named function in Excel using Excel 4.0 functions?

To create a named function using Excel 4.0 functions, go to the Formulas tab, open the Name Manager, create a new name, and then define the function (e.g., 'get.workbook(1)'). This creates a custom function that you can reference later.

What do you need to do to use the Excel 4.0 function 'get.workbook' to list sheet names?

You need to create a named function that refers to 'get.workbook(1)' and then use that named function (e.g., 'listSheetNames') in the worksheet to list the sheet names.

What is the difference between using Excel 4.0 functions in older versions of Excel versus modern versions?

In older versions of Excel, you need to use an array formula to handle multiple values, whereas in modern Excel, it automatically spills results across multiple cells without needing an array formula.

How can you list worksheet names in a vertical orientation?

To list worksheet names vertically instead of horizontally, you can use the modern 'transpose' function in Excel, which changes the orientation of the data from horizontal to vertical.

How do you remove the workbook name from the list of sheet names?

You can remove the workbook name by using the 'mid' function combined with 'len' to find the correct starting point of the sheet names after the curly bracket, and then adjust the formula accordingly.

How does the array formula work in older versions of Excel?

In older versions of Excel, you need to highlight the exact number of cells where the result will appear, type the formula, and then press 'Ctrl + Shift + Enter' to create an array formula.

What modern Excel function can be used to handle the horizontal-to-vertical conversion?

The 'transpose' function is used in modern Excel to change the orientation of data from horizontal to vertical or vice versa.

What steps should you take if you need to delete array formula results in Excel?

In older versions of Excel, you must highlight the entire range of cells that the array formula populates and delete them all at once. You can't delete just one cell from an array formula.