Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Excel 2003

    Does the below formula list all the sheet names in a workbook?
    I can't get it to work

    =ISNUMBER(ROWS(INDIRECT("'"&WorksheetNameHere&"'!A 1")))


    TIA
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    No, the formula returns either TRUE or FALSE.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='773540' date='03-May-2009 16:45']No, the formula returns either TRUE or FALSE.[/quote]

    Does a technique exist which returns the name of a worksheet?
    Regards
    Don

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you want to return the name of the worksheet to be returned in a cell, you can use the formula

    =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

    where A1 can be any cell on the worksheet. This formula will only work if the workbook has been saved to disk; it'll return an error value in a new workbook that hasn't been saved yet.

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I use the following to create a Table of content list of sheets. I insert a blank worksheet as the first sheet in the book and run the following macro. I've commented out the line that add a hyperlink to the A1 cell in

    Code:
    Sub ListSheets()
    Dim i As Integer
    With Cells(1, 1)
    	.Value = "Sheet Name"
    	.Font.Bold = True
    End With
    
    For i = 2 To Worksheets.Count
    With Cells(i, 1)
    	.Value = Worksheets(i).Name
    	'.Hyperlinks.Add anchor:=Cells(i, 1), Address:="", _
    	SubAddress:="'" & Worksheets(i).Name & "'!A1"
    End With
    Next
    
    Cells(1, 1).EntireColumn.AutoFit
    End Sub

  6. #6
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    Excel 2003
    Does the below formula list all the sheet names in a workbook?
    I can't get it to work
    Return a list of worksheet names, try to use ……...

    1) Define (Insert > Define > Name) the following...

    Name : WorksheetsName

    Refers to, enter :

    =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),)&T(NOW())

    Click OK

    2) Then try the following formula...

    =IF(ROW(1:1)>COUNTA(WorksheetsName),"",INDEX(Works heetsName,ROW(1:1)))


    Regards
    Bosco

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='bosco_yip' post='773573' date='04-May-2009 10:11']Return a list of worksheet names, try to use ...

    1) Define (Insert > Define > Name) the following...

    Name : WorksheetsName

    Refers to, enter :

    =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),)&T(NOW())

    Click OK

    2) Then try the following formula...

    =IF(ROW(1:1)>COUNTA(WorksheetsName),"",INDEX(Works heetsName,ROW(1:1)))


    Regards
    Bosco[/quote]

    Great! This did the trick
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  8. #8
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='773560' date='04-May-2009 06:42']If you want to return the name of the worksheet to be returned in a cell, you can use the formula

    =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

    where A1 can be any cell on the worksheet. This formula will only work if the workbook has been saved to disk; it'll return an error value in a new workbook that hasn't been saved yet.[/quote]


    Hans

    Not sure how this works. Its shows only Sheet1 but not others
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  9. #9
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='bosco_yip' post='773573' date='03-May-2009 22:11']Refers to, enter :

    =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),)&T(NOW())[/quote]
    Thanks for that Bosco

    Can you help me understand what's going on with the formula we place in the 'Name' > 'Refers to' box?

    T.I.A.
    Regards
    Don

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    GET.WORKBOOK is an old Excel 4 macro function. It can't be used in cell formulas, but it does work in defined names.

    The help file for Excel 4.0 Macro Functions is still available from Microsoft: Macrofun.exe File Available on Online Services.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    This formula =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)+ will display the name of the sheet containing the cell with the formula.

    You could replace A1 with a reference to a cell on another sheet (for example MyOtherSheet!A1) but that kind of defeats the purpose.

  12. #12
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Copy the formula down a enough rows to cover the number of sheets in the workbook.
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

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