Use GET.WORKBOOK to List Worksheet Names without VBA
Summary
TLDRIn 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
📊 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.
🔄 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.
Mindmap
Keywords
💡Excel 4.0 functions
💡Visual Basic
💡Worksheet names
💡GET.WORKBOOK function
💡Name Manager
💡Array formula
💡Transpose function
💡LEN function
💡MID function
💡Formulas Tab
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
関連動画をさらに表示
5.0 / 5 (0 votes)