Results 1 to 8 of 8
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,154

    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:2013
    KISS - Keep It Simple Stupid

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

    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:2013
    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,154

    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:2013
    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

  8. #8
    New Member
    Join Date
    Sep 2012
    Posts
    1

    Default Re: List worksheet names using a formula

    This rocks! I can now extract data from the same cell of every sheet, something I have struggled with for ages

    regards
    David

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