Formula as text in cell
 

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

Formula as text in cell
 

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
---------------
Jacob Skaria


"Akhare" wrote:

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

Formula as text in cell
 

"Akhare" wrote:
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 -----

"Akhare" wrote in message
...
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

Formula as text in cell
 

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 -----

"JoeU2004" wrote in message
...
"Akhare" wrote:
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 -----

"Akhare" wrote in message
...
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