This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

The hidden door to extra functions in Excel Part four of a six-part series of blogs
Did you know that you could combine range names and legacy Excel 4 functions to list out the worksheets or range names in your workbook - wthout using macros? I didn't either - here's how to do this, and much more! The hidden door to extra functions in Excel A little background Using Excel 4 functions with range names Example - Listing Worksheets (this blog) Other ideas for the use of Excel 4 functions References for this blog

The hidden door to extra functions in Excel

Part four of a six-part series of blogs

Did you know that you could combine range names and legacy Excel 4 functions to list out the worksheets or range names in your workbook - wthout using macros? I didn't either - here's how to do this, and much more!

The hidden door to extra functions in Excel
A little background
Using Excel 4 functions with range names
Example - Listing Worksheets (this blog)
Other ideas for the use of Excel 4 functions
References for this blog

Posted by Andy Brown on 15 September 2022

In this blog

Creating the range name
Transposing and filtering the results
Getting the hyperlinks

Example - Listing Worksheets

So how do we go from here:

The initial list of worksheets returned from GET.WORKBOOK (as described below and in the previous part of this blog).

To here:

The final clickable list of worksheets.

Gradually!

To keep the length of this blog down, I haven't explained every function in detail (in particular you may find my blog on the new spill array functions in Excel useful as reference).

Creating the range name

Here's the range name you should create:

The formula is shown in full below.

Here's the formula used:

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

This gets each worksheet name including its file name - like this:

The path to one of my worksheets.

The FIND function determines the position of the closing square bracket ] within the string of text (it's actually the 30th character); the REPLACE function replaces everything up to this point with an empty string of text.

Transposing and filtering the results

To tidy things up, we want to present the list of worksheets in a column (and exclude the current worksheet, which is called Directory):

This formula takes the row of names returned, filters them to exclude the current worksheet by name and then transposes the results into a column.

Getting the hyperlinks

Just for the sake of completeness, here's how to get clickable links to each worksheet:

The formula for this is shown below.

Here's the formula used:

=HYPERLINK("#" & B4 & "!A1","Go to " & B4 & " sheet")

The arguments used are:

Argument	What it shows
1	The worksheet and cell reference to go to (eg #Cover!A1)
2	What to display in the cell (eg Go to Cover sheet)

Parts of this blog
The hidden door to extra functions in Excel A little background Using Excel 4 functions with range names Example - Listing Worksheets (this blog) Other ideas for the use of Excel 4 functions References for this blog