Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I'd like to be able to use a worksheet name as part of a formula.
    The worksheet name has been derived from the formula:
    [pre]=RIGHT(CELL("filename",MainInput!$A$1),LEN(CELL("f ilename",MainInput!$A$1))-FIND("]",CELL("filename",MainInput!$A$1),1))[/pre]
    where MainInput is the originating worksheet

    The result is obviously MainInput.

    If the user changes the worksheet name then the output is updated accordingly.

    I would then want to use that worksheet name as part of a formula,
    i.e. the result would be "=MainInput!C2*B2"

    It's easy enough to include the link as normal entry.
    If the formula is entered simply as "=C2*B2", then when it is copied the formula stays as "=C2*B2", but the user wants to be able to copy this worksheet AND retain the reference to MainInput worksheet.

    Is this making sense?

    My alternative at present is for 2 worksheets - MainInput and 2nd alternative calc worksheet that already includes the link to MainInput.

    Thanks
    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can use the INDIRECT function for this. Let's say that the formula

    =RIGHT(CELL("filename",MainInput!$A$1),LEN(CELL("f ilename",MainInput!$A$1))-FIND("]",CELL("filename",MainInput!$A$1),1))

    is in cell A1. The formula

    =INDIRECT("'"&A1&"'!C2")

    will return the value of cell C2 on the sheet whose name is contained in cell A1. (The single quotes ' are needed if the sheet name contains spaces.) So if A1 contains MainInput, the result will be equivalent to

    =MainInput!C2

    but if A1 contains Monthly Data, the result will be equivalent to

    ='Monthly Data'!C2

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you Hans, reading up on it now.

Posting Permissions

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