can I dsplay the sheet name in a cell
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: can I dsplay the sheet name in a cell

  1. #1
    Guest

    Default

     
    Is there a way to display a sheet name in a
    cell

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is an odd question. Within excel I do not know of a way that you can print the worksheet name in a cell. However, I just wrote this function to see if it would work and it pretty much did so you can try it.

    Function WorksheetName() As String
    ActiveSheet.Activate
    WorksheetName = ActiveSheet.Name
    End Function

    Paste this in the visual basic editor and then you can write, in a cell, "=worksheetname()" and the name of the worksheet will appear in the cell. One caveat, though, the code gives instructions to print the active sheets' name in the cell, therefore, if you can find a way to view a sheet that's not the active sheet, the cell will print the wrong name. I don't know if you can even do that. Anyway, I hope this helps you a little bit.

    Dave

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-05 13:53, Anonymous wrote:
    Is there a way to display a sheet name in a
    cell
    In B1 enter:

    =CELL("filename",A1)

    In C1 enter:

    =RIGHT(B1,LEN(B1)-SEARCH("]",B1))

    Note. B1 and C1 can be any other unused cells.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Or in one cell:

    =RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))

    Cell("filename"), I just learned, returns the path to your file including the current sheet that is active, with the workbook in brakets (so the end of the path looks like this: [book1]Sheet1). The right function cuts out the rest of the path and leaves you with just the sheet. However, if the workbook you are working with HAS NOT BEEN SAVED, the cell("filename") function will return a blank string (""). If you were having trouble with this, make sure you save the workbook.

    Dave

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-05 14:52, davers5 wrote:
    Or in one cell:

    =RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))

    Cell("filename"), I just learned, returns the path to your file including the current sheet that is active, with the workbook in brakets (so the end of the path looks like this: [book1]Sheet1). The right function cuts out the rest of the path and leaves you with just the sheet. However, if the workbook you are working with HAS NOT BEEN SAVED, the cell("filename") function will return a blank string (""). If you were having trouble with this, make sure you save the workbook.

    Dave
    The reason I suggest using 2 cells is to avoid computing the same thing many times.

    And,

    =CELL("filename",A1)

    is better than just

    =CELL("filename")

    Aladin

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    A bit off the subject, but I got code from someone on how to show which sheet a cell is linked to. For example, if you have a cell in Sheet 2 linked to Sheet 1, you can have the adjacent cell tell you which sheet it is linked to.

    http://www.mrexcel.com/board/viewtop...c=2229&forum=2

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    When using the cell("filename") function, it's safer to include ,a1 option because it assures that formula references the file and sheet the formula is on and not the file and sheet that you last did a recalc on.


    It's never too late to learn something new.

    Ricky

User Tag List

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