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

function to get active worksheet name

This is a discussion on function to get active worksheet name within the Excel Questions forums, part of the Question Forums category; Hi, Is there a function where I can get the name of an active worksheet?...

  1. #1
    New Member
    Join Date
    Jul 2002
    Posts
    4

    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
    13,993

    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
    13,993

    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?

Page 1 of 2 12 LastLast

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