Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: function to get active worksheet name

  1. #1

    Default

    Hi,

    Is there a function where I can get the name of an active worksheet?

  2. #2
    MrExcel MVP
    Join Date
    May 2002
    Posts
    14,143

    Default

    beachboy:

    welcome to the baord!

    if you mean a worksheet function, try:

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

    Paddy

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    On 2002-07-17 20:20, PaddyD wrote:
    beachboy:

    welcome to the baord!

    if you mean a worksheet function, try:

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

    Paddy
    Hi Paddy:
    I hope you wouldn't mind my butting in for throwing in the following alternative using the MID function:

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

    it may be a little shorter than the one using the RIGHT function, but not a whole lot.

    Regards!

  4. #4
    MrExcel MVP
    Join Date
    May 2002
    Posts
    14,143

    Default

    Yogi,

    not seen your butt, but you should feel free to put it anywhere you like!

    Paddy

  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default

    Alternatively, you can use a UDF to do the job.

    ==========
    Function NM()

    NM = Application.Caller.Worksheet.Name

    End Function
    ===========

    ------------
    Function NM()
    Application.Volatile
    NM = ActiveSheet.Name
    End Function
    ------------

    The second one will update the formula and return the name of the active sheet, no matter where it is called from in the workbook.

    Bye,
    Jay

  6. #6
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701

    Default

    If no one minds me butting in either, you can insert a defined name and call it SheetName and refer it to:
    =GET.CELL(5,A1)

    Then in your worksheet you can call the function by setting any cell=SheetName

    SheetName will return the name of that sheet.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  7. #7
    Board Regular RichardS's Avatar
    Join Date
    Feb 2002
    Location
    Victoria, Australia
    Posts
    761

    Default

    Al Chara

    This doesn't work for me. Do I enter the formula
    =SheetName
    in another cell? When I do that, it returns 0. Any ideas?

    Richard
    If absence makes the heart grow fonder and familiarity breeds contempt, perhaps my wife should live in Darwin?

  8. #8
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701

    Default

    On 2002-07-17 22:56, RichardS wrote:
    Al Chara

    This doesn't work for me. Do I enter the formula
    =SheetName
    in another cell? When I do that, it returns 0. Any ideas?

    Richard
    This is what you do. Goto INSERT|NAME|DEFINE
    Type SheetName under "Names in workbook:"
    Type =GET.CELL(5,A1) under "Refers to:"

    Then go into a cell on your worksheet and type SheetName
    It will return the Sheet name
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  9. #9
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701

    Default

    On 2002-07-17 22:56, RichardS wrote:
    Al Chara

    This doesn't work for me. Do I enter the formula
    =SheetName
    in another cell? When I do that, it returns 0. Any ideas?

    Richard
    Richard,

    You are right, I pulled the wrong function. This one returns the value of the cell. I have to lookup the correct XLM function to use.

  10. #10
    Board Regular RichardS's Avatar
    Join Date
    Feb 2002
    Location
    Victoria, Australia
    Posts
    761

    Default

    Thank God for that, I thought I was going balmy.
    If absence makes the heart grow fonder and familiarity breeds contempt, perhaps my wife should live in Darwin?

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