Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sheet name in a cell (2000 SR-1)

    Anyone know of a formula (or VBA code) that will return the sheet name in a cell on that sheet (isn't necessarily the active sheet).

  2. #2
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet name in a cell (2000 SR-1)

    Have created this, but happy to hear fom anyone that can do better!

    =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("filename",A1)))

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sheet name in a cell (2000 SR-1)

    Youi could use he following VBA function to return th ename of a sheet from a given tab order (index)<pre>Function SheetName(intSheet)
    SheetName = Sheets(intSheet).Name
    End Function</pre>


    = SheetName(3) will return the name of the third sheet in the workbook, which typically would be Sheet3 if the sheets had the default names and order. However if you drag sheet 3 and place it before sheet 2, then the above would return Sheet2, which would be the third tab. It is most usefull if you do not reorder the sheets.

    Andrew C

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet name in a cell (2000 SR-1)

    ....and then there are these three related UDF's that I use. Not sure where I first found them but it would almost certainly be here or hereabouts.

    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Function GetSheetName(ref) As String
    GetSheetName = ref.Parent.Name
    End Function
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Function GetWorkbookName(ref) As String
    GetWorkbookName = ref.Parent.Parent.Name
    End Function
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Function GetAppName(ref) As String
    GetAppName = ref.Parent.Parent.Parent.Name
    End Function
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    where ref is a cell reference eg a1

    HTH

    .... actually - I've never used the last of these. Has anybody else? And if so why?


    Brooke

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet name in a cell (2000 SR-1)

    Brooke,

    This little jewel just saved me from sure insanity...
    <pre>Function GetSheetName(ref) As String
    GetSheetName = ref.Parent.Name
    End Function
    </pre>


    I continue to learn from all of you xl-perts... <img src=/S/read.gif border=0 alt=read width=19 height=33> ...Thanks!

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Sheet name in a cell (2000 SR-1)

    Hi Brooke,
    Just a small point - I would add Application.Volatile to the start of those functions (not sure it's really necessary for the last one) to make sure that they update if, for example, you rename a sheet.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet name in a cell (2000 SR-1)

    You can find a few alternatives in <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=47756&page=&view= &sb=&o=&vc=1> this thread </A>.

Posting Permissions

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