Excel Help: How to reference tab name in cell

Discussion in 'Computers' started by Mark Giles, Jun 26, 2006.

  1. Mark Giles

    Mark Giles Second Unit

    Joined:
    Aug 30, 2002
    Messages:
    272
    Likes Received:
    0
    In Microsoft Excel: Can someone tell me the formula on displaying the name of the worksheet (tab name) in a cell on the sheet? I'm not talking about the header/footer code, but in a cell.
    I know theres a way to do it, I just forget what the formula is.
    Thanks in advance!! [​IMG]
     
  2. Mark Giles

    Mark Giles Second Unit

    Joined:
    Aug 30, 2002
    Messages:
    272
    Likes Received:
    0
    Ok, nevermind. i found it...

    =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
     
  3. me90201

    me90201 Auditioning

    Joined:
    Jul 14, 2011
    Messages:
    1
    Likes Received:
    0
    =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))
     
  4. mrtonothorns

    mrtonothorns Auditioning

    Joined:
    Jul 18, 2011
    Messages:
    2
    Likes Received:
    0
    SWEET! Now can someone tell me how to find the name of the cell that is to the left of the tab I am putting the formula in?
     
  5. mrtonothorns

    mrtonothorns Auditioning

    Joined:
    Jul 18, 2011
    Messages:
    2
    Likes Received:
    0
    SWEET! Now can someone tell me how to find the name of the cell that is to the left of the tab I am putting the formula in?
     
  6. Still a novice

    Still a novice Auditioning

    Joined:
    Aug 21, 2012
    Messages:
    1
    Likes Received:
    0
    Did you ever get an answer to the question regarding the name of the worksheet to the left?
    If so, I would appreciate you sharing it with me.
    Thank you
     
  7. Kirk Lammert

    Kirk Lammert Auditioning

    Joined:
    Nov 13, 2012
    Messages:
    2
    Likes Received:
    0
    You can use the same formula with a minor alteration to the CELL function. The original formula:
    =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))
    assumes that you want the name of the tab you're currently on. However, there is a second argument to the CELL function for reference. Using this argument you can get the tab name of any tab within your worksheet. It works like so:
    =RIGHT(CELL("filename",'OtherTab'!A1),LEN(CELL("filename",'OtherTab'!A1))-FIND("]",CELL("filename",'OtherTab'!A1)))
    where "OtherTab" is the tab you want to get the name of. I used cell A1 of that sheet, but any cell will do.
    Hope this helps!
     
  8. Y Jin

    Y Jin Auditioning

    Joined:
    Dec 18, 2012
    Messages:
    1
    Likes Received:
    0
    Kirk, that's very helpful!
    I have more than 1 tabs in my spreadsheet book. If I want a specific cell in each tab read its own tab name what should I do? I used your first formula, all tabs read one tab name.
    Any suggestion? Thanks!
     
  9. Mr SteveW

    Mr SteveW Auditioning

    Joined:
    Dec 26, 2012
    Messages:
    1
    Likes Received:
    0
    Hi Kirk or Mark:
    I have a strange result using your formulas for returning value of the same sheet you are on and wondering if you're experiencing the same. If I rename any other sheet name in the entire workbook (or any other open workbook for that matter), and the formula (even though referencing the sheet I am on) changes value to show me the name of the most recently modified sheet name. Doesn't matter which book I'm in.
    If I recalculate the formula (click in cell and press Enter) it goes back to showing me the correct "current sheet name". If I close the book and reopen it, the book opens with the correct "current sheet name". So it seems like it experiences only a temporary glitch, but can cause problems on formulas within the page while I'm working in my workbook... any ideas on how to keep it from changing temporarily?
    Thanks.
     
  10. ryates

    ryates Auditioning

    Joined:
    Jan 7, 2013
    Messages:
    1
    Likes Received:
    0
    Hi All: Yes, i find that in multiple sheets the cell reference returns the name of the first or current sheet tab. What would be helpful is if i cound have a relative reference to the sheet tab name unique to each sheet.
     
  11. MikeR128

    MikeR128 Auditioning

    Joined:
    Jan 22, 2013
    Messages:
    1
    Likes Received:
    0
    You just need to set the reference to an absolute cell on the sheet. Thus cell("filename","$A$1") will alway only refer to the current sheet where it appears and =RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1),1)) will always give the correct Tab reference.
     
  12. snowwhiteeg

    snowwhiteeg Auditioning

    Joined:
    Mar 23, 2013
    Messages:
    1
    Likes Received:
    0
    Real Name:
    snowwhiteeg
    RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))
     
  13. Sam Posten

    Sam Posten Moderator
    Moderator

    Joined:
    Oct 30, 1997
    Messages:
    21,080
    Likes Received:
    1,939
    Location:
    Aberdeen, MD & Navesink, NJ
    Real Name:
    Sam Posten
    This thread delivers.
     

Share This Page