Create formula from user input

[fdgsogc]

I am creating an Excel template that will build a report based on responses to a questionnaire and based on how the questions are answered, different text will appear in the report on second worksheet.

However, I don't want to hard code the formulas for the text output. I want an admin type user to configure the conditions that result in the text output. See my attached spreadsheet for an example.

MY PROBLEM:
I cannot get the user's input to translate into an IF statement.

 

[fdgsogc]

Here is a proper working link to the sample file.

 

[GlennUK]

If you make a valid boolean expression in Excel you can use a defined name formula to evaluate it, BUT, your expression is NOT a valid boolean expression ... your expression ( when the string parts are concatenated ) is:

IF(c9=yesANDc10equalsno)AND)c11greater than2)AND)c12less than2)

sa you can see, it doesn't make sense, and is not a valid Excel formula. In boolean expressions in Excel comparisons are ANDed together by use of the AND function, and not by an expression named AND. Your parentheses are not balanced, and your comparison values are not quoted. Your operators are also invalid.

If I make a string ( in a cell, say, D19 ) of

AND(d9="yes",d10="no")

and then do menu command Insert/Name/Define, of name EvalCase1, with contents of =EVALUATE(Sheet1!$D$19), I can then use this name in a cell, like this:

=IF(EvalCase1,"works","fail")

That explains how you get a string to evaluate. I think your major problem is going to be trying to design a tool for the admin user to be able to create a valid boolean expression from choices ( if it's to be truly free-form ).

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[fdgsogc]

Thanks Glenn.

I'm getting where you're going with this. However, I don't have an EVALUATE function in my Excel 2007. Is this found with an Excel Add-in?

 

[GlennUK]

I said to use the EVALUATE function within a defined name. I said to do this for a reason ... the reason being is that the EVALUATE function is not part of normal Excel functions, it is part of the old Excel XLM macro language, and can only be used within a macro sheet OR a defined name formula.

I used this method so that you wouldn't have to do any VBA programming.

Cheers, Glenn.

Beauty is in the eye of the beerholder.