Formula challenge: create a list of sheet names based on a prefix

posted Jan 10, 2014, 8:25 AM by Krisztina Szabó   [ updated Jun 12, 2014, 2:48 AM ]
Freddy: When I give the word, throw the first switch.
Igor: You've got it, master.
Freddy: Get ready.
Freddy: Get set.
Freddy: Go.
Freddy: Throw the second switch.
Freddy: Throw the third switch.
Igor: Not the third switch?


by The FrankensTeam

A formula which lists sheet names according to a rule could be useful for example to summarize data from particular sheets. But could it be done without VBA, using Excel formulas?
We would like to give the possibility to the readers who are interested in formula challenges to think about this problem.

The challenge:
In the file sheets are named according to a rule: prefix and number, so for example:
prefix = “Company”
sheets:
Company1
Company2
Company3
Company5
The numbering is not necessary to be continuous.

The task is to create a formula which will produce the list of existing sheet names in array: {“Company1”;“Company2”;“Company3”;“Company5”}
And it will automatically capture the newly added sheets too.
So, if you would like to think… do not scroll down!

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Two formula solutions

Solution with ERROR.TYPE

The idea is to test the existence of the possible sheet names ( prefix&ROW($1:$100) ) and acquire the numbers which results existing sheet. Then concatenate these numbers with the prefix will give the list we wants to have.

=prefix & SMALL( IF( ERROR.TYPE(INDIRECT(prefix&ROW($1:$100)&"!A1"))=3 , ROW($1:$100) ), ROW( INDIRECT("1:"&SUM( --(ERROR.TYPE(INDIRECT(prefix&ROW($1:$100)&"!A1"))=3)) ) ) )
to be array-entered

How does it work?
The test is based on INDIRECT formula, which returns the reference specified by a text string. If the reference is invalid (so the sheet does not exists) the result is #REF! error. Using ERROR.TYPE we can check the result: it will give 3 if the reference exists and 4 if not. The test statement is nested to an IF formula, where you can see the array of the same numbers we concatenated to the prefix. The false argument of IF is omitted, so the result of the formula will contain numbers and FALSE values only, for example:
{1;2;FALSE;FALSE;5;6;7;FALSE;FALSE;FALSE;FALSE;.....}
The reason of leaving out the false argument (no comma separator is used!) is that we will use SMALL function in the next step, and in SMALL logical values are not counted.
SMALL function will help to choose the numbers from this array. We need a correctly dimensioned array of numbers from 1 to the number of existing sheets - this is the second argument of SMALL.


Solution using IFERROR and AREAS:

=prefix & LARGE( IFERROR( AREAS(INDIRECT(prefix&ROW($1:$100)&"!A1"))*ROW($1:$100) ,0), ROW($A$1:INDEX($A:$A,SUM(IFERROR(AREAS(INDIRECT(prefix&ROW($1:$100)&"!A1")),0)))) )
to be array-entered

How does it work?
The base logic is the same, only the test part is different. We used a relatively rarely used formula: AREAS. The argument of the formula is a reference (or in our case: array of references, generated by the INDIRECT function). The result of the formula is the number of areas in the reference. Area is a range of continuous cells or a single cell. In our case, the references contain only one cell (A1) so the result will always be 1 or #REF! error if the sheet does not exist:
{1;1;#REF!;#REF!;1;1;1;#REF!;#REF!;#REF!;#REF!;.....}
After multiplying it by an array of the same numbers concatenated to the prefix, then put it into an IFERROR to replace the #REF!s with 0, we have this array:
{1;2;0;0;5;6;7;0;0;0;0;.....}
Now we can use LARGE to retrieve the non-0 numbers. The second argument of LARGE is built with the same logic as in the first solution for SMALL.

Based on these solutions you can create your own formula for different rules, for example year numbers.
Please remeber, adding a new worksheet does not trigger calculation, but renaming a sheet triggers it, so the formula will be re-calculated after renaming the sheet.


Example

Maybe you have already read the previous post about how to sumif from more sheets. This sheetname-list challenge originates from that post, so here we share a file contains the full model: sumif from more sheets with the possibility of adding new sheets with prefix-based names. Please download the file - you can find the first solution as name: MySheets.


UDF solution

Finally, here is a user defined function with the possibility of excluding sheets from the sheet list. You can add the sheet names to be excluded as a range or a text array. If the argument is empty, the result will contain all the sheets of the workbook.


Function ListSheets(Optional vExcludeSheets As Variant)
    Application.Volatile
  
    Dim asSheetNames() As String
    Dim vExcludedName, vWorkSheet As Variant
    Dim i As Long, bNeed As Boolean
  
    For Each vWorkSheet In ThisWorkbook.Worksheets
        bNeed = True
        If Not IsMissing(vExcludeSheets) Then
            If IsArray(vExcludeSheets) Or TypeOf vExcludeSheets Is Range Then
                For Each vExcludedName In vExcludeSheets
                    If vExcludedName = vWorkSheet.Name Then
                        bNeed = False

                        Exit For
                    End If
                Next
            Else
                If vExcludeSheets = vWorkSheet.Name Then
                    bNeed = False
                End If
            End If
        End If
        If bNeed Then
            ReDim Preserve asSheetNames(i)
            asSheetNames(i) = vWorkSheet.Name

            i = i + 1
        End If
    Next
    ListSheets = Application.Transpose(asSheetNames)
End Function


Formulas: Igo-r, IngaKris
UDF: Igo-r

Leave a comment