Results 1 to 6 of 6

Activesheet.name as a formula not code?

This is a discussion on Activesheet.name as a formula not code? within the Excel Questions forums, part of the Question Forums category; I wish to return the name of the sheet in cell D1. I know how to do this in code. ...

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Hilo, Hawaii
    Posts
    240

    Default Activesheet.name as a formula not code?

    I wish to return the name of the sheet in cell D1. I know how to do this in code.
    But, is it possible to do with just a formula? =(SheetName) or some such.

    I wish to use this as a template for my 18 facilities and have the sheet refer to D1 where ever the facility name needs to be. This way I just have to type it one time.

    Any help or questions are appreciated.

    KniteMare

  2. #2
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: Activesheet.name as a formula not code?

    This is one way --

    =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Hilo, Hawaii
    Posts
    240

    Default Re: Activesheet.name as a formula not code?

    Thanks that does the trick. Now as to why does that work???
    KniteMare


    EDIT Follows
    OK I got it.

    Return all the text to the right of the ] char.
    By finding the position of the ] and subtracting that from the total length
    and returning that many char from the right.

    Thanks so much for the help.
    End EDIT
    Knite Mare

  4. #4
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: Activesheet.name as a formula not code?

    The CELL function is an information function, one of whose arguments can be "filename" -- see the help file for the others. [2nd arg = reference of 1st arg]

    However, by itself, CELL... returns the Full name, including path/file/sheet, as in --

    C:\Documents and Settings\Administrator\My Documents\MrExcel\[FUNCTIONS.xls]Sheetname reference

    In order to strip out just the sheetname, we want the RIGHT most portion only, of a length equal to the length of the entire above string less the position number of the closing "]" around the filename: in this case, the LEN function returns 95, the FIND function returns 76, leaving 19.
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Hilo, Hawaii
    Posts
    240

    Default Re: Activesheet.name as a formula not code?

    Right, I looked up each piece of the function in my trusty MicroSoft Excel 97 Worksheet Function Reference, AFTER you pointed me in the correct direction. Without your assistance I would never have thought to Look up the CELL function as a starting point for this procedure.
    KniteMare

  6. #6
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: Activesheet.name as a formula not code?

    Congrats with the refernce material! We like seeing that!
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

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