Formula as text in cell

[Akhare]

Hello

Is it possible to do a lookup on a table containing formulae and apply the
derived formula.

The table of formulae may contain for example the following values

A sum(a1:a2)
B sum(b1:b2)
C sum(c1:c2)

I cannot use nested IF statements as the actual formulae used by me are long
and extend over 1024 charaters.

Could anyone please provide me some way to achieve this?

Thanks

 

[Jacob Skaria]

If the cell contents are not formulas but ranges as below then you can use
INDIRECT()

A1 a1:a2
B1 b1:b2
C1 c1:c2

=SUM(INDIRECT(A1))
is same as SUM(a1:a2)

If this post helps click Yes

 

[JoeU2004]

Is it possible to do a lookup on a table containing formulae and
apply the derived formula.

Does one of the following ideas help?

1. Instead of a table of formulas in text form, have a table of formulas,
all of which are evaluated. Simply select the result using a lookup
function or CHOOSE. For example:

X1: =if(A1=B1,1,2)
X2: =if(A2=B2,3,4)
C1: =if(and(1<=C2,C2<=2),choose(C2,X1,X2),"")

Of course, that example could be done more simply. It is only a
paradigm for more complex formulas.


2. Set up X1:X2 as a table of formulas in text form (like the above, but
without "="), and define a named formula ("doit") as follows (Insert > Name

Define > Refers To):

=if(and(1<=$C$2,$C$2<=2),choose($C$2,$X$1,$X$2),"")

Then in C1, you can put:

=doit

Caveat: Oddly, the named formula does not seem to be volatile.
Consequently, I need to press ctrl+alt+F9 every time a change occurs that
would affect the outcome of the named formula . Perhaps you or someone
else can find a work-around. Mine is #3 below.


3. Similar to #2, but instead of a named formula, define a UDF as follows:

Function doit(fml As String)
Application.Volatile
doit = Evaluate(fml)
End Function


----- original message -----

 

[JoeU2004]

Errata....

I wrote;

2. Set up X1:X2 as a table of formulas in text form (like the above, but
without "="), and define a named formula ("doit") as follows (Insert >
Name > Define > Refers To):

=if(and(1<=$C$2,$C$2<=2),choose($C$2,$X$1,$X$2),"")

The named formula should be:

=if(and(1<=$C$2,$C$2<=2),evaluate(choose($C$2,$X$1,$X$2)),"")


Also....

I neglected to mention that generally I prefer #1 over any solution that
utilizes formulas in text form. The problem with the latter is that cell
references in the table of formula will not be updated automagically if you
insert rows or columns or move referenced cells.


----- original message -----