Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Use sheet name (WinNT4, XL97)

    Is there a way to tell XL that this cell should contain the name of the sheet it's in, using something like a function? eg =name(worksheet) or something?
    Beryl M


  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use sheet name (WinNT4, XL97)

    From Chip Pearson's web site:

    To return the sheet name, without the path, use

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

    This will return "MySheet"

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Use sheet name (WinNT4, XL97)

    It does, indeed, Preston, many thanks for that - I'm b**ggered if I can see why or how, but it does!

    Cheers!
    Beryl M


  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Use sheet name (WinNT4, XL97)

    The "info type" of the CELL function gives the full path, the filename within brackets and then the sheetname:
    c:Path[filename.xls]SheetName

    FIND("]",CELL("filename",A1))+1
    gets the position of the char right after the right bracket(]) (this is the starting position of the sheetname)

    LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))
    Gets the length of the sheet name (from the end of the string to the bracket)

    The MID pulls out that part (the sheetname)

    Steve

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Use sheet name (WinNT4, XL97)

    Thanks, Steve. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    It's all so simple when you know how, isn't it?!
    Beryl M


  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Use sheet name (WinNT4, XL97)

    <hr>It's all so simple when you know how, isn't it?!<hr>

    Isn't that always the case <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Steve

Posting Permissions

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