Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Construct formula (2007)

    Hi. I have the names of other worksheets running across row 2, and wish to pick up values in the correspond sheets from cell B4 (for example). How can i construct a formula that uses a sheet name contained in a cell?
    On a similar note, can I have the current worksheet name appear in a cell? Thanks, Andy.

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

    Re: Construct formula (2007)

    Say you have a worksheet name in cell C2.
    In another cell, enter the formula
    <code>
    =INDIRECT("'"&C2&"'!B4")
    </code>
    In workbooks that have been saved, you can use the following esoteric formula to return the name of the sheet containing the formula:
    <code>
    =REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")</code>

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Construct formula (2007)

    Thank you very much. Andy.

  4. #4
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Construct formula (2007)

    To return the current worksheet name.

    The another option,

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

    Regards
    bosco

Posting Permissions

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