Hello,
I would like to setup a formula using a IF() statement that can be
customized
based on a definition set in a particular cell instead of being hard-
coded.
eg I don't mean just a custom VALUE but also a custom TEST.
Can this be done?

Let's say I have a formula 1:
IF(LEFT(A1)=a),"yes","no")

if I now want to change the formula to a new formula 2:
IF(RIGHT(A1)=b),"yes","no")

instead of manually rewriting the formula, I'd like to just define
the test and value to be used in a separate cell and refer to it.

Example:

- cell A1 contains the data to be tested.
- cell A10 contains the "TEST": for example LEFT()
- cell A11 contains the "VALUE", for example "a"
the formula should be (pseudocode):
IF("test specified in $A$10 applied on" (A1)=$A$11, "yes","no").

If I now want to apply "formula 2", I would just need to modify cells
A10 and A11:
A10 - RIGHT() (or any other formula of course)
A11 - b

Here is a User-defined function:

Function eval(func As String)
Application.Volatile
eval = Evaluate(func)
End Function

This will take a string and evaluate it as if it were a normal
formula. So, for example, put the text 2 + 4 in A1, and then in A2
enter this formula:

=eval(A1)

and you will get 6 as a result.

So what you will need to do is to build up your string as a
syntactically correct Excel formula and then just pass it to the eval
function to get the result.

Hope this helps.

Pete


On Oct 5, 6:00 pm, MCSmarties wrote:
Hello,
I would like to setup a formula using a IF() statement that can be
customized
based on a definition set in a particular cell instead of being hard-
coded.
eg I don't mean just a custom VALUE but also a custom TEST.
Can this be done?

Let's say I have a formula 1:
IF(LEFT(A1)=a),"yes","no")

if I now want to change the formula to a new formula 2:
IF(RIGHT(A1)=b),"yes","no")

instead of manually rewriting the formula, I'd like to just define
the test and value to be used in a separate cell and refer to it.

Example:

- cell A1 contains the data to be tested.
- cell A10 contains the "TEST": for example LEFT()
- cell A11 contains the "VALUE", for example "a"
the formula should be (pseudocode):
IF("test specified in $A$10 applied on" (A1)=$A$11, "yes","no").

If I now want to apply "formula 2", I would just need to modify cells
A10 and A11:
A10 - RIGHT() (or any other formula of course)
A11 - b