Formula as text in cell

  • Thread starter Thread starter Akhare
  • Start date Start date
A

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
 
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
 
Akhare said:
Is it possible to do a lookup on a table containing formulae and
apply the derived formula.
Click to expand...

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):
Click to expand...

=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 -----
 
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),"")
Click to expand...

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