# Dynamically-linked formula, cell reference in the string

0

#### 0-0 Wai Wai ^-^

Hi.
I have a series of formula which use the same condition.
Eg:
C1 contains the string of the testing condition, [eg IF(AND(\$D1=\$E1, \$F1=0),
"", ]
G1-Z1 make reference to condition in A1, [eg ={call the condition in C1}, {some
function to do}]

C2 contains the string of testing condition, [eg IF(AND(\$D2=\$E2, \$F2=0), "", ]
G2-Z2 make reference to condition in A1, [eg ={call the condition in C2}, {some
function to do}]

** Problem **
1)
How can I call the testing condition (to be used by other cells)?
Note: I need to do so because I can update the testing condition once when
there're changes.
Otherwise I need to update each of them which is tedious.

2)
For cell reference in the string, it will not be updated when I drag the formula
along the table.
Does anyone know how to slove this problem?

--
- I'm using Office XP
- I'm using Windows XP

¥»¤Hªº¯à¤O«D±`¦³­­. ¦p¦³¤£·í¤§³B, ±æÃÑªÌ¤£§[½ç¥¿!!
After all, the above are merely my little opinion/idea.
Since my ability is limited, I could be wrong.

H

#### Héctor Miguel

hi, 0-0 Wai Wai ^-^ !
I have a series of formula which use the same condition. Eg:
C1 contains the string of the testing condition, [eg IF(AND(\$D1=\$E1,\$F1=0),"", ]
G1-Z1 make reference to condition in A1, [eg ={call the condition in C1}, {some function to do}]
C2 contains the string of testing condition, [eg IF(AND(\$D2=\$E2, \$F2=0), "", ]
G2-Z2 make reference to condition in A1, [eg ={call the condition in C2}, {some function to do}]
1) How can I call the testing condition (to be used by other cells)?
Note: I need to do so because I can update the testing condition once when there're changes.
Otherwise I need to update each of them which is tedious.
2) For cell reference in the string, it will not be updated when I drag the formula along the table.
... how to slove this problem?

1) I don't know what EXACTLY is there in 'A1'... -> {some function to do} )
assuming there is some... 'pseudo-formulae' coding like: D1&E1&F1 ... perhaps: D5*E5
-> 'something' like a 'real' function/formula BUT starting not with the equal sign -?-

2) using named-formulae [insert > name > define...] and the old-fashion 'evaluate' xl4-macro function...
a) select 'G1' > define a name [i.e. SomethingToDo]
use the following formula: =and(!\$d1=!\$e1,!\$f1=0)+0*now()
b) add/define another name [i.e. DoSomething]
use the following formula: =evaluate(!\$a1)
-> please, note the 'signs' -> ! IT IS IMPORTANT !

3) now you can use the A1's {some function to do} in G1:Z1 as in the following manner:
[G1] =if(somethingtodo,dosomething,"") or... [i you plan to use in several cells]...
[G1] =choose(1+somethingtodo,"Nothing to do!",dosomething)

NOTE: there is a possibe 'risk' of an xl-crash... while defining names as suggested...
if you copy the worksheets that uses them -?- so...
use this proposal on your own... risk, criteria, modifications, etc.

HTH,
hector.
¥»¤Hªº¯à¤O«D±`¦³­­. ¦p¦³¤£·í¤§³B, ±æÃÑªÌ¤£§[½ç¥¿!!
After all, the above are merely my little opinion/idea.
Since my ability is limited, I could be wrong.

p.s. I liked the 'slogan'... but can't understand the 'original' text