Translating string to formula

[boim]

Suppose I have two columns Speed and Duration and they're defned with relative row absolute column in Names (i.e., Speed - "=$A2", Duration - "=$B2") .

On another range/sheet I define another Name: Distance that refers to a cell that contain a string: "Speed * Duration" (i.e., Distance - "=$AA$1, and cell AA1 contains the string "Speed * Duration").

I'd like to have a third column after Speed and Duration above that use the formula defined by the string pointed by Distance. Is it possible and what is the formula for the cells in the 3rd column in which I'd like to use the Distance formula? It seems possible but I can't figure out how. I'm trying to avoid event-based macro at this moment to keep things simple.

Note: The formula is defined in a string above so that in case the formula change (maybe to "Speed * 3 * Duration"), the result will be instantly reflected in the 3rd column.

Thanks.

 

[DonkeyOte]

You could make use of old Evaluate Call from a Name ?

Name: EVAL
RefersTo: =Evaluate(INDIRECT("RC[-1]",FALSE))

So if the cell to the left contains string: Speed * 3 * Duration

In adjacent cell to right:

=EVAL

should return the result (based on your names row position of the above formula would of course be important!)


Note if you were to use

=Evaluate(DISTANCE)

rather than earlier method and modified either Speed/Duration value you would need to use CTRL + ALT + F9 to have the above recalculate - the former method would not.

 

[boim]

Thanks that's what I've been looking for. Is Evaluate being phased out by Microsoft, I can't find it with auto-fill formula nor in help file with Excel 2007 except in the VBA section. I kind of vaguely remember that function in Excel 2003...