Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Name of sheet containing this cell

    I'd like to put a formula into a cell that contains the name of the sheet that contains the cell.

    After looking through an earlier discussion, I came up with the following formula:

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

    However, the CELL() function refers to properties of the active cell rather than the cell containing the formula. So for example if:
    Sheet2!A1 contains
    =MID(CELL("filename"),FIND("]",CELL("filename"))+1,100)
    Sheet1!A1 contains
    =Sheet2!A1

    Then when Sheet1 is active, it will display "Sheet1" not "Sheet2". If the calculations in Sheet2 depend on its name (which is why I want the name there) they will be incorrect.

    Any suggestions?

    Ian.

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name of sheet containing this cell

    Take a look <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=32824&page=&v iew=&sb=&vc=1#Post32824>here</A>

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name of sheet containing this cell

    Thanks, kjktoo, but that's what I found before. It gives the name of the sheet containing the active cell at any given time, which need not be the cell containing the formula.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name of sheet containing this cell

    The CELL function accepts a second argument, a cell reference. try adapting your formula like this:

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

    This will always give the sheet name the formula is on.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name of sheet containing this cell

    Thanks Jan. Just what I needed! (and thanks again kjktoo - I should have read the earlier post more carefully)

    All the best

    Ian.

Posting Permissions

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