562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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!
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.
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.
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|
Some other pages relevant to the above blogs include:
25 Aytoun Street