Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Location
    Annapolis, Maryland, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    reference tab name (2003 SP3)

    I think this is probably an easy question. Is there a way to make a cell reference the name of a tab (another worksheet)? How?

    Thanks!
    Michael Coleman
    BOMI International

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: reference tab name (2003 SP3)

    Only with VBA code.

    A line of code similar to: Activesheet.Name = Range("A1").Value

    It will need to be linked to a worksheet event if it must happen more automatically. If so...please specify more detail of what you want to have happen.
    Regards,
    Rudi

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: reference tab name (2003 SP3)

    If you meant that you want to return the sheet name of a cell, you can use a formula like this:
    <code>
    =MID(CELL("filename",Sheet2!A1),FIND("]",CELL("filename",Sheet2!A1))+1,256)
    </code>
    This will only work if the workbook has been saved to disk, not in a newly created workbook.

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: reference tab name (2003 SP3)

    Wow...and I read that question exactly the other way around. This could have been in the puzzle forum. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rudi

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: reference tab name (2003 SP3)

    I'm not sure what Michael's question means - I just wanted to provide an alternative.

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: reference tab name (2003 SP3)

    What a useful formula, it solves a problem I didn't even know I had.

    Thank you.

    StuartR

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: reference tab name (2003 SP3)

    ..so use cell("address",
    ..instead of cell("filename",
    and tweak formula a bit to get rid $ address etc (find "!" perhaps?) and make allowances for spaces in sheet names and then..
    ..I think it will work even if the workbook has NOT been saved to disk and will work in a newly created workbook

    zeddy

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: reference tab name (2003 SP3)

    Thanks. On the other hand, the formula I posted will also work when the cell is on the same worksheet, while CELL("address", ...) will only return the cell address without the sheet name if the cell is on the same sheet.

    The OP can choose which formula suits his purposes best.

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: reference tab name (2003 SP3)

    Hi Hans

    I knew you knew that.

    zeddy

Posting Permissions

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