# Thread: formula from text (2003)

1. ## 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. ## 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. ## Re: formula from text (2003)

Thank you. That seems to have done the trick.

4. ## 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. ## 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. ## 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
•