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.

Outlines

00:00

📊 Discovering Excel 4.0 Functions for Listing Worksheets

In this segment, the focus is on utilizing Excel 4.0 functions to list down the names of worksheets without resorting to Visual Basic. Excel 4.0 functions are older macro functions that have been largely replaced by modern Visual Basic and Excel functionalities. Despite their age, these functions are still accessible in Excel and can be used effectively. The video demonstrates how to use the 'GET.WORKBOOK' function with the number '1' to retrieve a list of worksheet names. This is done by accessing the 'Name Manager' under the 'Formulas' tab to create a named reference. The video also shows how to apply the function to a sample workbook with multiple worksheets, highlighting the need to manually refresh the formula to update the list of worksheet names.

05:00

🔄 Transposing and Refining Worksheet Names in Excel

This part of the video script explains how to manipulate the list of worksheet names obtained from the 'GET.WORKBOOK' function. It covers the process of transposing the list from a horizontal to a vertical orientation using the 'TRANSPOSE' function available in modern Excel versions. Additionally, the script addresses the issue of the workbook name appearing in the list and demonstrates how to remove it using the 'MID' function. The 'MID' function is used to extract the worksheet name by finding the position of the closing curly bracket and adjusting one character to the right, then extracting the length of the worksheet name using the 'LEN' function. The video concludes with a reminder to copy the formula and offers assistance for any questions or requests related to Excel.

Keywords

💡Excel 4.0 functions

Excel 4.0 functions refer to an older set of macro functions that were once integral to Excel but have since been superseded by more modern functionalities and Visual Basic for Applications (VBA). In the video, these functions are highlighted as a way to perform tasks that once required significant computational power, illustrating the evolution of Excel and its capabilities. The script specifically mentions that these functions can still be used in modern Excel, showcasing their enduring utility.

💡Visual Basic

Visual Basic, or VBA, is a programming language that is used to create macros and automate tasks in Excel. The video contrasts Excel 4.0 functions with VBA, indicating a shift from older macro functions to a more powerful and flexible programming environment. VBA allows for more complex automation and customization, which is why it has replaced many of the older Excel 4.0 functions.

💡Worksheet names

In the context of the video, 'worksheet names' refers to the titles of the different sheets within an Excel workbook. The video's main objective is to demonstrate how to list these names without using VBA, which is typically used for such tasks. The script outlines a method using Excel 4.0 functions to extract and display worksheet names, highlighting a practical application of these older functions.

💡GET.WORKBOOK function

The GET.WORKBOOK function is an Excel 4.0 function that is used to retrieve information from a workbook. In the video, it is specifically used with the argument '1' to list down the names of the worksheets. This function is part of the old macro functions that the video aims to demonstrate, showing viewers how to use it to achieve a task that would typically require VBA or newer Excel features.

💡Name Manager

The Name Manager is a feature in Excel that allows users to create, edit, and delete named ranges or formulas. In the video, it is used to define a named formula 'list sheet names' that utilizes the GET.WORKBOOK function. This tool is essential for the video's tutorial, as it enables the creation of a custom function that can be easily referenced and used throughout the workbook.

💡Array formula

An array formula in Excel is a formula that performs operations on an array of values and returns a result for each element. The video explains how to use an array formula when working with older versions of Excel, where the user must highlight the cells they expect to be populated, enter the formula, and then press Ctrl+Shift+Enter to execute it. This is contrasted with newer versions where formulas can automatically fill adjacent cells.

💡Transpose function

The Transpose function in Excel is used to change the orientation of data, typically converting rows into columns or vice versa. In the video, it is used to rearrange the list of worksheet names from a horizontal to a vertical list. This function is part of the modern Excel functionalities that provide more straightforward methods for data manipulation compared to the older Excel 4.0 functions.

💡LEN function

The LEN function in Excel returns the number of characters in a text string. In the video, it is used in conjunction with the MID function to extract the names of the worksheets without including the workbook name. This demonstrates a method for string manipulation that is essential for cleaning and formatting data within Excel.

💡MID function

The MID function in Excel is used to return a specific number of characters from a text string, starting at a specified position. In the video, it is used to extract the worksheet names from a string that includes the workbook name. This function is crucial for the video's goal of listing worksheet names without the extra text, showcasing a technique for text manipulation within Excel.

💡Formulas Tab

The Formulas Tab in Excel is a section of the Ribbon interface that provides access to various functions and tools related to formulas and calculations. In the video, it is where the Name Manager is accessed, which is essential for creating the custom named formula used to list worksheet names. This tab is a central location for formula-related tasks in Excel.

Highlights

Introduction to continuing the journey in discovering Excel 4.0 functions.

Excel 4.0 functions are old macro functions replaced by modern Visual Basic and Excel functionalities.

Older computers relied on functions to perform tasks due to less powerful hardware and less smooth interfaces.

Excel 4.0 functions can still be used in Excel if known how to use them.

The 'GET.WORKBOOK' function can list down the names of the worksheets without using Visual Basic.

The number '1' in 'GET.WORKBOOK(1)' signifies getting the list of worksheet names.

A 683-page manual exists detailing Excel 4.0 functions, available online.

To use Excel 4.0 functions, go to the Formulas tab and use the Name Manager.

Create a named reference 'list sheet names' that refers to the 'GET.WORKBOOK(1)' formula.

After setting up the function, use it by typing '=list sheet names' in a cell.

The function lists worksheet names horizontally based on their arrangement in the workbook.

Refreshing the formula is required when the order of worksheets changes.

In older Excel versions, use an array formula by highlighting cells and pressing Ctrl+Shift+Enter.

Transposing the list from horizontal to vertical is possible using the TRANSPOSE function in modern Excel versions.

To remove the workbook name from the list, use the MID and LEN functions to extract just the worksheet names.

The final formula provided in the video removes the workbook name and lists worksheet names vertically.

The video concludes with an invitation for questions and requests for future Excel tutorial topics.

Transcripts

hi everyone in today's video we're going

to continue our journey in discovering

Excel

4.0 uh functions so in this uh video we

want to list down the names of the

worksheet without using Visual Basic now

like what was mentioned in the previous

video the Excel 4.0 functions are old

macro functions that are currently

replaced by the more modern Visual Basic

and more modern Excel

functionalities you have to realize that

back in the days our computers are not

so powerful the interface is not as

smooth of this so Excel has to rely on

functions to perform many of the tasks

so even if these are old forgotten

functions you can actually still use

them them in Excel as long as you know

how to use them now for this one we want

to list down the names of the worksheets

and there's actually a macro Excel 4.0

function that can do that it's going to

be the

get a workbook function so it's get.

workbook and then open parenthesis and

then you have to put a certain number

here so the number is represented uh

represents different information that

you can derive okay uh in the workbook

and I don't have all the equivalences

here but you can actually find them in

their internet somebody made a 683 page

uh manual about Excel 4.0 functions now

but specifically though the one that

will list down the worksheet names is H

one so we have to use get that workbook

open parenthesis one because that number

signifies that you want to get the list

of the names of the

worksheets so let's do that let me first

erase

this so now in order to use a Excel 4.0

function you have to go to the formulas

Tab and then under the formulas tab

we're going to use the name manager

which allow us to provide named cells or

named references or even formulas so

from here we're going to click

new and we're going to see this small

pop up we're going to assign a name for

our quote and quote function going to be

list sheet

names and it's going to refer to a

certain formula so for this one I'm

going to say equals

get. workbook open parenthesis and then

comma

one then now I'm going to click okay and

if your problem has no hiccups it will

just proceed as

usual and then I'm now going to click

close since my function is now ready to

go now I prepared here a file that has a

worksheet let me swap them around so we

have sheet one sheet four sheet three

and another random worksheet

so I'm now going to extract those names

by using the function that I did so

equals and then I'm going to say list

sheet names and you will see that it's

being suggested to me now so I could

just Tab and unlike other functions that

requires you to do an open parentheses

and then arguments since this is a

reference I don't have to use that I

just have to hit enter at this point and

and you will see that I will have a

horizontally uh oriented list of

worksheets the arrangement of which is

based on what we have here so sheet one

sheet four sheet three and then March

2003 if I swap the worksheets around it

will not automatically update you have

to refresh the formula by um going back

to the first cell and then hit enter

now the functionality of excel wherein

it appeals to the other cells is not

available in the early versions of excel

this can only be done in the recent

version of excel

ms65 so if you are using Excel 2019 or

earlier you need to perform this a

little differently so you have to do the

same as what I did with the name manager

but the execution is where the

difference will lie so instead of just

typing one cell you have to highlight

four cells because we expecting four

values for the result and then while

these four cells are highlighted you

will type equals and then you will call

on the function that we

created and since we're using we're

simulating an old Excel approach you

don't just enter you have to convert

this into an array

formula so instead of enter you have to

click all together

control shift and then enter so that

would make the formula put the answers

on the celles that you've highlighted so

you have to highlight the exact number

or the number of cells that you you're

expecting to be

populated and that's it we now manage to

list down the names of the

worksheets and let's um let's transpose

it like for example I want it to be um

vertically oriented instead of

horizontal so let me remove this one

first and take note if you used an old

version of excel you have to highlight

them all and delete Al together because

you cannot delete just the first cell

okay Excel will not allow you to do that

you have to highlight all four of them

and then delete now in order to change

this into a vertical arrangement I will

use one of the more modern functions not

an not an Excel 4.0 function it's going

to be the transpose

function so in modern iterations of

excel the transpose function picks up

ranges of a range of cells and changes

their orientation from vertical to

horizontal and vice versa so since this

is horizontal if I enter it will convert

into a um vertically oriented

result now the next problem that we want

to fix here is the presence of the name

of the file that is also going to be

part of the result I want to remove the

book one from the

reference so from here I could update my

formula so I would say a mid open

parenthesis so mid of this

cells

comma then I have to indicate to Mid

where will I start the thing is we don't

know exactly how where to start but we

know that if we

find the close curly

bracket in my list of sheet names that

is where

the uh worksheet name will start right

at least after it so I have to type a

plus one so instead of giving mid a

fixed value I have to

find the close bracket in my list of

worksheets but adjust one character to

the right because that's where the

worksheet name actually begins one

character after the Clos bracket comma

now we don't know exactly how many

characters there are in the name of the

worksheets so I will have to use Len

which will give me the length of the

characters of the uh names of the

worksheet and then

close now I can also close the mid

function close the transpose function

hit enter and now I have a list of of

worksheet names without

the uh function or without the workbook

name so here's the formula feel free to

copy

it and with this okay I'm going to also

put this in the uh comment or in the

description of this video I hope this

video help you okay uh list down the

worksheet names without using

macros and if ever you have questions

feel free to use the comment I'll try to

answer as soon as I can and if ever you

like this video please uh I would

appreciate a like and a

subscribe and if you have any requests

or questions in Excel also use the

comment section and I will try to make a

video for you if I know the answer and

that's it for this video I'll see you in

the next

one