Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
545 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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
Search our website
We also send out useful tips in a monthly email newsletter ...
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!
|
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).
Here's the range name you should create:
The formula is shown in full below.
Here's the formula used:
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.
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.
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:
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 |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.