Page 1 of 2 12 LastLast
Results 1 to 10 of 15
Like Tree2Likes

Get Sheetname in Formula

This is a discussion on Get Sheetname in Formula within the Excel Questions forums, part of the Question Forums category; Hi, Just a quick one.. tried the archives, but can't seem to find the answer.. How do I get the ...

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    49

    Default

    Hi,
    Just a quick one.. tried the archives, but can't seem to find the answer..
    How do I get the current sheet name using formulas instead of vba??
    Thanks!

  2. #2
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

    there will be shorter ways:

    =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
    Chasden likes this.

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Portland, OR
    Posts
    602

    Default

    I don't know about the current sheet name but here is how you get the active sheetname:

    SheetName=Activesheet.Name

  4. #4
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

    "...using formulas instead of vba" !

  5. #5
    New Member
    Join Date
    Mar 2013
    Posts
    4

    Default Re: Get Sheetname in Formula

    Quote Originally Posted by John Yazou View Post
    Hi,
    Just a quick one.. tried the archives, but can't seem to find the answer..
    How do I get the current sheet name using formulas instead of vba??
    Thanks!
    Use below formula anywhere in the sheet to get the sheet name.

    =REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")

  6. #6
    Board Regular
    Join Date
    Jul 2009
    Location
    Woking UK
    Posts
    1,869

    Default Re: Get Sheetname in Formula

    @YasarShazad

    Thanks.

    Something I've been looking for too.
    Mike

    -----------------------------------------------
    Some solutions don't require an IF!

    When posting code wrap your code between [CODE] tags.

    Paste your Excel data...

    MrExcel HTML Maker or Excel Jeanie

  7. #7
    New Member
    Join Date
    Dec 2011
    Posts
    40

    Default Re: Get Sheetname in Formula

    Quote Originally Posted by YasarShahzad View Post
    Use below formula anywhere in the sheet to get the sheet name.

    =REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")
    This formula doesn't work for files with multiple sheets/tabs. For example, if you're keeping a monthly tracker, with the months as Sheet Names (March 2013, April 2013, etc.), and you put this formula on every sheet, it will show the same month on every sheet (the month of whichever sheet you entered the formula in last). So if you add the formula to the March 2013 sheet, all of the other sheets will also change to March 2013.

    Quote Originally Posted by PaddyD View Post
    there will be shorter ways:

    =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
    This one works as intended, with a different month on each sheet.

  8. #8
    New Member
    Join Date
    Mar 2013
    Posts
    4

    Default Re: Get Sheetname in Formula

    Quote Originally Posted by Chasden View Post
    This formula doesn't work for files with multiple sheets/tabs. For example, if you're keeping a monthly tracker, with the months as Sheet Names (March 2013, April 2013, etc.), and you put this formula on every sheet, it will show the same month on every sheet (the month of whichever sheet you entered the formula in last). So if you add the formula to the March 2013 sheet, all of the other sheets will also change to March 2013.



    This one works as intended, with a different month on each sheet.
    I tried and it works just fine

  9. #9
    New Member
    Join Date
    Dec 2011
    Posts
    40

    Default Re: Get Sheetname in Formula

    Quote Originally Posted by YasarShahzad View Post
    I tried and it works just fine
    I tried it, and it doesn't.

    Perhaps it only works on certain versions of Excel.

    Here's what i did in Excel 2003:
    0. Save the spreadsheet. Neither formula works if the spreadsheet is new and unsaved.
    1. Create two tabs: "March 2013" and "April 2013."
    2. Enter your formula into a cell in the March sheet. It will return "March 2013."
    3. Enter your formula into a cell in the April sheet. It will return "April 2013."
    3a. Go back to the March sheet. It now says "April 2013."
    4. Fix the error by re-entering the formula into the March sheet. It now says "March 2013" again.
    4a. Go to the April sheet. Now it also says "March 2013."

    The longer formula posted earlier in the thread does not do this.
    Last edited by Chasden; Apr 12th, 2013 at 03:13 PM.

  10. #10
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,333

    Default Re: Get Sheetname in Formula

    This
    CELL("filename")
    Is a Volatile function, it recalculates every time anything is changed in the book.
    Any time anything changes on any sheet, then Cell("filename") is recalcuated (on ALL sheets)
    But it returns the sheet of whichever sheet is currently active to ALL occurances of the Cell("filename") function.


    By changing it to
    CELL("filename",A1)

    That "Ties" the Cell function to a cell on that sheet.
    So each one on each sheet is dependant on the A1 in it's own sheet.
    Chasden likes this.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

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