Turn text into formula

[aenimosity7]

I have a cell (Cell 1) with a drop down list containing three choices. Each choice is the name of a range (I.e. Range 1, Range 2, and Range 3). I have another cell (Cell 2) which uses the INDIRECT function to reference Cell 1. What I want to do is take Cell 2 one step further by saying "if Cell 1 equals Range 1, do X function to Range 1, if Cell 1 equals Range 2, do Y function to Range 2, if Cell 1 equals Range 3, do Z function to Range 3. To make things more complicated, I don't want to use nested if statements because I would rather not hard code the commands into the formula. One idea I had was to make a two column table, with Range 1, Range 2, etc., in column 1, and the command in column 2 (i.e. do X for Range 1, do Y for Range 2, etc.). That would allow me to add additional ranges and corresponding commands in the future. However, I have had no luck trying that strategy.

Can anyone think of a way to do this? I can post a workbook if that would help. Thanks!

 

[mikerickson]

If you have A1 containing a dropdown with the three options "Range1", "Range2" and "Range3"

Define 3 named Formulas like
Name: Range1 RefersTo: =SUM(Sheet1!$A$10:$A$100)
Name: Range2 RefersTo: =PRODUCT(Sheet1!$B$5:$B$8)
Name: Range3 RefersTo: =AVERAGE(Sheet1!$C$8:$C$12)

Define one more formula
Name: EvalFormula RefersTo: =EVALUATE(Sheet1!$A$1)+ROW(INDIRECT("A1"))-1

If you put =EvalFormula in a cell, it will show the value you want, depending on what is selected from the Drop Down.

(The ROW(INDIRECT("A1"))-1 is needed to make EvalFormula volatile and respond to any change in any of the three sub-ranges. A more complicated, less volatile do nothing calculation trigger could be devised.)

 

[Aladin Akyurek]

I have a cell (Cell 1) with a drop down list containing three choices. Each choice is the name of a range (I.e. Range 1, Range 2, and Range 3). I have another cell (Cell 2) which uses the INDIRECT function to reference Cell 1. What I want to do is take Cell 2 one step further by saying "if Cell 1 equals Range 1, do X function to Range 1, if Cell 1 equals Range 2, do Y function to Range 2, if Cell 1 equals Range 3, do Z function to Range 3. To make things more complicated, I don't want to use nested if statements because I would rather not hard code the commands into the formula. One idea I had was to make a two column table, with Range 1, Range 2, etc., in column 1, and the command in column 2 (i.e. do X for Range 1, do Y for Range 2, etc.). That would allow me to add additional ranges and corresponding commands in the future. However, I have had no luck trying that strategy.

Can anyone think of a way to do this? I can post a workbook if that would help. Thanks!

Excel does not allow one to write formulas that write formulas, not really.

An option would be something like:

=CHOOSE(MATCH(Cell1,RangeList,0),SUM(INDIRECT(Cell1)),SUMIF(INDIRECT(Cell1),">0"),INDEX(INDIRECT(Cell1),X2))

where Cell1 is data-validated with a list consisting of Range1, Range2, and Range3, each of which is a non-dynamic reference.

 

[T. Valko]

If you have A1 containing a dropdown with the three options "Range1", "Range2" and "Range3"

Define 3 named Formulas like
Name: Range1 RefersTo: =SUM(Sheet1!$A$10:$A$100)
Name: Range2 RefersTo: =PRODUCT(Sheet1!$B$5:$B$8)
Name: Range3 RefersTo: =AVERAGE(Sheet1!$C$8:$C$12)

Define one more formula
Name: EvalFormula RefersTo: =EVALUATE(Sheet1!$A$1)+ROW(INDIRECT("A1"))-1

If you put =EvalFormula in a cell, it will show the value you want, depending on what is selected from the Drop Down.

(The ROW(INDIRECT("A1"))-1 is needed to make EvalFormula volatile and respond to any change in any of the three sub-ranges. A more complicated, less volatile do nothing calculation trigger could be devised.)

What does "less volatile" mean?

=EVALUATE(Sheet1!$A$1)+N(T(NOW()))

 

[mikerickson]

"Less volatile", triggered by fewer cells. In my example

=EVALUATE(Sheet1!$A$1) + COUNTA(Sheet1!$A$10:$A$100,$B$5:$B$8,$C$8:$C$12) - COUNTA(Sheet1!$A$10:$A$100,$B$5:$B$8,$C$8:$C$12)

would trigger recalculation only if the selection from A1 or one of the cells in the predicenant ranges was changed.

 

[T. Valko]

"Less volatile", triggered by fewer cells. In my example

=EVALUATE(Sheet1!$A$1) + COUNTA(Sheet1!$A$10:$A$100,$B$5:$B$8,$C$8:$C$12) - COUNTA(Sheet1!$A$10:$A$100,$B$5:$B$8,$C$8:$C$12)

would trigger recalculation only if the selection from A1 or one of the cells in the predicenant ranges was changed.

Ok, I see what you meant.

Thanks!