Results 1 to 7 of 7
Like Tree1Likes
  • 1 Post By T. Valko

List worksheet names using a formula

This is a discussion on List worksheet names using a formula within the Excel Questions forums, part of the Question Forums category; I don't know if this topic has been posted before (I didn't do a search!) but if anyone's interested here's ...

  1. #1
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default List worksheet names using a formula

    I don't know if this topic has been posted before (I didn't do a search!) but if anyone's interested here's a formula method to list worksheet names.

    This uses an old macro function.

    Create this defined name:

    Insert>Name>Define
    Name: SheetNames
    Refers to: =GET.WORKBOOK(1)&T(NOW())
    OK

    Then, to list the sheet names, entered in cell A1:

    =INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1))

    Copy down until you get errors. Use an error trap if you'd like!

    Some notes:

    The &T(NOW()) is used to make the formula volatile so that it calculates when a sheet name is changed.

    GET.WORKBOOK(1) returns the sheet names as a horizontal array.

    1 is the argument index number for returning the sheet names.

    Some examples of other uses:

    Return the sheet name of the nth sheet (n=2):

    =INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),2)

    How many sheet names start with the word Sheet:

    =SUMPRODUCT(--(LEFT(INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),0),4)="Sheet"))
    raygh likes this.
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  2. #2
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: List worksheet names using a formula

    Ooops!

    How many sheet names start with the word Sheet:

    =SUMPRODUCT(--(LEFT(INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),0),4)="Sheet"))
    "Sheet" has 5 characters so:

    =SUMPRODUCT(--(LEFT(INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),0),5)="Sheet"))
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  3. #3
    Board Regular MyExcel's Avatar
    Join Date
    Sep 2008
    Posts
    508

    Default Re: List worksheet names using a formula

    It is Great Biff
    really it is amazing way
    but u say
    This uses an old macro function.
    how i can get more examples and more Explains about This " old macro function."

  4. #4
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: List worksheet names using a formula

    You can download the macro function help files here:

    http://support.microsoft.com/kb/128185

    It will extract 3 files:

    Macrofun.cnt
    Macrofun.gid
    Macrofun.hlp

    Once extracted, just double click on Macrofun.hlp.
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  5. #5
    New Member
    Join Date
    Mar 2013
    Posts
    1

    Default Re: List worksheet names using a formula

    Quote Originally Posted by T. Valko View Post
    I don't know if this topic has been posted before (I didn't do a search!) but if anyone's interested here's a formula method to list worksheet names.

    This uses an old macro function.

    Create this defined name:

    Insert>Name>Define
    Name: SheetNames
    Refers to: =GET.WORKBOOK(1)&T(NOW())
    OK

    Then, to list the sheet names, entered in cell A1:

    =INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1))

    Copy down until you get errors. Use an error trap if you'd like!

    Some notes:

    The &T(NOW()) is used to make the formula volatile so that it calculates when a sheet name is changed.

    GET.WORKBOOK(1) returns the sheet names as a horizontal array.

    1 is the argument index number for returning the sheet names.

    Some examples of other uses:

    Return the sheet name of the nth sheet (n=2):

    =INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),2)

    How many sheet names start with the word Sheet:

    =SUMPRODUCT(--(LEFT(INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),0),4)="Sheet"))

    This method may return wrong information if there are more than one workbook opened.

    I wonder if that will be better to add two more steps:

    1. Enter formula "=CELL("filename",1:1048576)" in a defined cell, here I use Sheet2!$A$1

    2. Enter formula "=MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)-1)" in next cell, here I use Sheet2!$B$1

    3. Insert>Name>Define
    Name: SheetNames
    Refers to: =GET.WORKBOOK(1,'Sheet2'!$B$1)&T(NOW())

    4. Then, to list the sheet names, entered in cell A1:

    =INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1))

  6. #6
    New Member
    Join Date
    Dec 2012
    Posts
    1

    Default Re: List worksheet names using a formula

    can you plz explain step three?? i can understant how to execute it .

  7. #7
    New Member
    Join Date
    Jan 2014
    Posts
    3

    Default Re: List worksheet names using a formula

    Great work, but I was wondering if you could help me to use this formula to list all sheets that starts with Sheet instead of counting them! Thanks in advance

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com