Help on how to select one formula from a list of formulae

[Ninja Stevie]

Tricky question, not sure if it's possible withou the use of a macro.

What I want to do is have a list of formula in a table, eg numbered 1 -
10. I then want a cell to be able to take one of these formulae, and
apply it to figures local to it. I want to be able to alternate which
formula is looked up dynamically.

Vlookup doesnt work as the formula are imported exactly as-is. I cant
get indirect working with this as it imports 'text' only. Anybody have
any other ideas how to do this.

Cheers for any help

 

[Guest]

Not sure if I understand you correctly but it seems like something that the
CHOOSE function will be good at. Can see how to use it
http://www.auditexcel.co.za/lookupandreference.html . Hope this is what you
were looking for.

 

[Ninja Stevie]

Adrian,

Thanks, but this function appears to operate in a similar fashion to
indirect in that it pulls through the text rather than the formula.
I'll try to show an example below...


OK, using the R1C1 reference style, i have the following table
comprising formulae...

Formula table
1 =RC[-1]*10
2 =RC[-1]*100
3 =RC[-1]*1000
4 =RC[-1]*10000
5 =RC[-1]*100000

Now, I have a table containing an index number, a (random) 3 digit
value to which I want to apply one of the above formulae, and a cell
which I want the result of this calculation shown...

using choose function, the following appears:
3 654 =RC[-1]*1000
2 456 =RC[-1]*100
4 564 =RC[-1]*10000
5 489 =RC[-1]*100000
1 785 =RC[-1]*10
1 157 =RC[-1]*10

whereas I want the following result:

3 654 654000
2 456 45600
4 564 5640000
5 489 48900000
1 785 7850
1 157 1570

using choose, the actual 'values only' is copied from the formula
table, whereas I want the formula to be taken and then applied. Is this
possible?

Regards

 

[Herbert Seidenberg]

Here is how to use Choose.
Name the columns in your result table Pick, Numb, Result
Menu > Insert > Name > Define
Name your formulas Func1...Func5
Enter the formulas in the Refers To box
Example: Name in Workbook Func1
Refers To Numb R*10
(Note: The space is required)
In your Result column enter and copy down this formula
=CHOOSE(Pick R,Func1,Func2,Func3,Func4,Func5)