Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Build Worksheet Ref Formula (Excel 2000)

    I know there is a way to do this--but I can't recall and searches have been fruitless. All of this occurs in the same workbook. I am trying to build a formula that references the text in a cell where the text gives the name of the worksheet that the formula would refer to. For instance, if in cell C2 I have the formula =Nettles!E11, that would give say a value, 45.12, and this is the end result I want. So If cell A2 had the name Nettles, there is supposed to be some way to have a formula in B2 that uses the text in A2 to get the same result, 45.12. The usefulness is to be able to copy down that formula or merely change the text in A2. But I can't recall, so don't know if its usage of the Indirect function, Cell, or Address, or something or a combination. Anyone recall?
    Steve

  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

    Re: Build Worksheet Ref Formula (Excel 2000)

    This in B2 will give you the value of cell E11 if the sheet name in cell A2

    Steve
    =indirect("'"&A2&"'!E11")

  3. #3
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Build Worksheet Ref Formula (Excel 2000)

    That's it! It was the leading single quote mark that kept me messed up.
    Thanks a lot.
    Steve

  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

    Re: Build Worksheet Ref Formula (Excel 2000)

    You ONLY need to put in the single quote around the sheet name IF the sheet name contains a space.

    It DOESN'T HURT to have it in if it has NO space, so I find it good practice to just use it.

    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
  •