Indirect Formula Reference

Posted by Dave on April 09, 2001 6:59 PM

I would like to enter a formula into a cell as text, then have that formula calculated in a different cell. I know I can display formulas for the whole sheet, but I don't want to do that.

For example,
In A1, I would enter the value 3.

In B1, I would enter the value 'A1 + 5. This would be text, not a formula. Excel would display "A1 + 5"

In B2, I would like to know what I have to enter to have B2 copy the text from B1, evaluate the expression A1 + 5, and display 8.

This way, I could change the "5" in B1 to a 4 and see "7" in B2.

Thanks for your help.

Posted by Dave Hawley on April 09, 2001 10:35 PM

Hi Dave

Try this way:

In cell A1 type: 3
In Cell B1 type: 5
In cell C1 type: A1
=SUM(INDIRECT(C1),B1)

Dave

OzGrid Business Applications

Posted by Aladin Akyurek on April 09, 2001 10:56 PM

===============

Dave

What follows isn't much but a fast response.

A simplistic idea would be:

B2 =INDIRECT(LEFT(B1, SEARCH("+",B1)-1)) + RIGHT(B1,LEN(B1) - SEARCH("+",B1))

This formula expects a plus as math operator in the expression that B1 holds.

Another, but also not very flexible, idea is:

B2 =Eval

Eval makes use of the EVALUATE command from the old Excel 4.0 macro language (for an interesting example, see The Excel Logic Page at
http://geocities.com/aaronblood).

Activate Insert|Name|Define, enter Eval as name and the following as Refers to:

=EVALUATE(INDIRECT(LEFT(!$B$1, SEARCH("+",!$B$1)-1)) & MID(!$B$1,SEARCH("+",!$B$1),1) & RIGHT(!$B$1, LEN(!$B$1) - SEARCH("+",!$B$1)))

What we really would want is a built-in Excel function similar to EVAL that you find in the programming language Lisp. However, such an addition might upset the system that spreadsheets are.

Hope this helps.

Aladin