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 three 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 (this blog) Example - Listing Worksheets Other ideas for the use of Excel 4 functions References for this blog

The hidden door to extra functions in Excel

Part three 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 (this blog)
Example - Listing Worksheets
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 a range name using an Excel 4 function
Using a range name referencing an Excel function

Using Excel 4 functions with range names

It won't take long for you to realise that Excel has never heard of Excel 4 functions in normal use:

There's no point continuing: the GET.WORKBOOK function clearly isn't available for use.

However, you can use these functions in Name Manager, as follows.

Creating a range name using an Excel 4 function

First launch Name Manager from the Formulas tab of the ribbon:

You can press Ctrl + F3 to start this too.

Create a new range name:

Click on this button to create a new range name.

Give your range name a name, and say what it refers to:

Here we're saying that instead of this range name referring to a cell or block of cells (as range names normally do), it acts like a function. Read on for how to then use it!

If you're wondering what Get.Workbook does, and why we used the number 1 in brackets, see the last part of this blog for how to download a compendium of the full syntax of all of the Excel 4 functions available for you to use.

In our list of range names, the one we've just created doesn't really stand out:

The only sign that this range name is different is the Refers to column, showing that it doesn't refer to a cell or cells at all.

Using a range name referencing an Excel function

Because your range name contains a formula you can use it as a function in Excel:

When you start typing the range name in, Excel has clearly heard of it!

When you choose the range name, it works - sort of:

The function returns a horizontal row of the names in a workbook, each of which contains the file name too. We just need to neaten this up a bit, which we'll do in the next part of this blog - but we have proof of principle!

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