Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Chatham, Ontario, Canada
    Posts
    86
    Thanks
    5
    Thanked 1 Time in 1 Post

    formula from text (2003)

    Is it possible to create a formula which is comprised in part of text from a cell?
    The formula which I need to create will be similar to ='[april ''06 monthly report.xls]Aprpg4'!$L$9+E9
    I'd like to be able to enter the name of the spreadsheet (or perhaps the sheet and page name) into a cell and then concatenate it with the page and cell information to make the formula.
    Is this feasible or am I heading off in the wrong direction?

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

    Re: formula from text (2003)

    You can use the INDIRECT function. A limitation of INDIRECT is that it only works with references to other workbooks if they are open; an indirect reference to a closed workbook results in #REF.

    One way to get around this is to put all worksheets in the active workbook, or to create worksheets in the active workbook that link to external worksheets. You can then use INDIRECT to refer to the sheets in the active workbook.
    Another solution is the INDIRECT.EXT function from Laurent Longre's free Morefunc add-in, available from Excel add-ins.

    Here is an example of using the INDIRECT function: if the cell L1 contains the text Aprpg4, the formula
    <code>
    =INDIRECT("'"&$L$1&"'!$L$9")+E9
    </code>
    will evaluate as if it was
    <code>
    ='Aprpg4'!$L$9+E9</code>

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Chatham, Ontario, Canada
    Posts
    86
    Thanks
    5
    Thanked 1 Time in 1 Post

    Re: formula from text (2003)

    Thank you. That seems to have done the trick.

  4. #4
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula from text (2003)

    This is a fantastic formula. Thanks for sharing.

    I modified it a bit to do some counting, and I've run into a problem and keep getting an error.

    I can get this formula to work:
    =COUNTA(INDIRECT("'"&A22&"'!B2:B600"))
    It's going to column B in a referenced spreadsheet and counting the number of non-empty cells.

    However, when I use the following formula to do a countif statement, I am receiving a Syntax error and it highlights the "Y" as the source of the problem. Any ideas what I'm doing wrong?
    =COUNTIF(INDIRECT("'"&A4&"'!C3:C15,"Y"))

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

    Re: formula from text (2003)

    The quotes and parentheses are placed incorrectly. Try this:
    <code>
    =COUNTIF(INDIRECT("'"&A4&"'!C3:C15"),"Y")
    </code>
    The argument of INDIRECT is a string (which can be concatenated from several parts)

  6. #6
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula from text (2003)

    Thanks!

    That was driving me nuts. I thought I tried every combination.

Posting Permissions

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