Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Excel 2003. You have 10 sheets in a workbook. Each sheet/tab has a name. Anyway to make the sheet/tab name automatically go into Cell A1? Maybe some type of formula? Thank you!!!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

    Steve

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    If you want the sheet name to appear in A1 then the formula should refer to a different cell

    e.g. =MID(CELL("filename",A2),FIND("]",CELL("filename",A2))+1,256)

    and this formula does not work until the workbook has been saved.
    Regards
    John



  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    A good point of the file requiring being saved but it doesn't matter that the cell refers to itself, since it is a property it is getting and not using the value of the cell itself.

    Steve

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by sdckapr View Post
    but it doesn't matter that the cell refers to itself, since it is a property it is getting and not using the value of the cell itself.
    You are right. I thought it gave me an error when it referred to itself, but it doesn't now. So there must have been something else wrong with what I did.
    Regards
    John



  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    As you pointed out it gives an error if the file is not saved. But the error will not go away until the cell is "re-confirmed": <f2>-enter or editing it and enter.

    [I presume after saving you still had the error, and you assumed it was the circular reference and then edited it making the error go away and thus "confirmed" it must have been the circular reference when in fact it was the editing itself]

    Steve

Posting Permissions

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