Microsoft Excel 2007 home and student

I have a list of formula which i want to execute

when certain conditions are true, how can i

call those formula ?

below is clarification

a1 = 1

b1 = 2

c1 = Sum/Sub/Mul/Div (user will write any of

these manually)

d1 = [get result based on user choice]

z1 = "=a1+b1"

z2 = "=a1-b1"

z3 = "=a1*b1"

z4 = "=a1/b1"How can i perform this ...???

d1 will hold any condition or formula say i tried

to use if as below

=if(c1="sum",'execute z1', if(c1="Sub",

'execute z1' , if(c1="Mul", 'execute z3',

if(c1="Div", 'execute z4', "0"))))

also tried "Indirect" and searched google.com

but cant get proper answer.

if anybody knows this issue please solve it by

giving example, you can also use vba/macro

for this.

Hi, Here is one way to do it.

As noted there are two input values in cells A1 and B1

Cell C1 will use a validation list to allow a user to select the action required

In cells Y1 to Y4 enter the four actions:Y1=SUM, Y2=SUB etc.

Select cell C1 and apply data validation - and select List as the option and enter the list range in the formula box:

=$Y$1:$Y$4

In cells Z1 to Z4 enter the four formulas - not in quotes, but as actual formulas:

Z1 contains =A1+B1 and will show the result 3, Z2 contains =A1-B1 and shows -1 etc.

In cell D1 enter this formula which looks up the result based on the value selected in cell C1

(As the valid options in C1 come from the list in column Y, VLOOKUP() will always find a match, so there is no need to trap it's #NA error):

=VLOOKUP(C1,$Y$1:$Z$4,2,FALSE)Regards

thanks 4 kind ur reply, but i have also tried vlookup()

it only gives the result that is displayed in cell that is called,

suppose y2=div and z2 = a1/b1, the value that is returned will be

displayed, not the formula.

also let me tell you that these values will be changed as the data

will be entered.

can you tell me is there any possibility to attach file here, so i can send you,

i m using excel 2007 version

Hi, You said

it only gives the result that is displayed in cell that is calledbut that is what you asked for ... you said that you wanted to

callthe formula or "execute' it.What I gave you returns these values when you have 1 & 2 in cells A1 & B1:

SUM 3 SUB -1 MUL 2 DIV 0.5Which values are you referring to here:

also let me tell you that these values will be changed as the data

will be entered.If you mean the values in A1 & B1, then the results in D1 will respond to changes in A1 & B1

If A1 contains 21 and B1 contains 7 you get this:SUM 28 SUB 14 MUL 147 DIV 3Please post the results you are getting in cell D1 when you use the solution I proposed, and then add the results that you wanted to see in cell D1.

Regards

Hi,

sorry it was my mistake, i dont mean to call formula but

execute formula.

you are right, your second explanation is exactly what i want.

Let me tell u my real problem, i have a file of metal, steel and

iron materials like pipe, sheet, bars etc.. The user will insert

the name of material and type of material whether its type of

pipe, iron or bar, the corresponding formulas are written in

sheet, as the user insert the item name and its

dia/weight/length or related property, the corresponding

formula from list will be executed in Column No. D row will

change.Pls give me its solution, if you are not still clear about the

query, tell me.

Hi, The second 'solution' which calculates results based on two input cells and a formula (which is selected based on what is entered in C1) is what the solution I posted does, and it does it using the VLOOKUP function.

This is how it works - using the original example:

A1 and B1 contain user entered values, lets say 21 and 7

Y1 to Z4 contain a list of functions and their respective formulas. This is what the 8 cellslooklike:Y Z 1 SUM 28 2 SUB 14 3 MUL 147 4 DIV 3and this is what the cells contain:Y Z 1 SUM =A1+B1 2 SUB =A1-B1 3 MUL =A1*B1 4 DIV =A1/B1

The results of the four calculations are created by standard formulas using the data from A1 and B1.

Cell Z2 contains =A1-B1 i.e. 21-7 which shows 14.In C1 the user selects from the list of available functions.

If SUB is selected, the formula in D1 uses VLOOKUP, to find the item in C1 in the column of cells Y1 to Y4, and then returns the value from the adjacent column of cells Z1 to Z4.When SUB is entered in C1, VLOOKUP finds SUB in cell Y2 and returns the value 14 from cell Z2.

With SUB selected in C1 if the user changes the values in A1 and B1 the result in D1 will change.

As a result I think that the solution I originally posted will work for you. Obviously the formulas will be more complicated and they will refer to more than two cells for the variables, such as thickness, diameter, length ...

but as long as each formula is alongside it's product description, then the user will get the result appropriate for the product description they have selected.Regards

i have performed the same thing, but cant get success.

If you have calculated this in Excel sheet, can you mail me (email address deleted), if you dont mind please ?

so that i can clear my doubt.

hope u dnt mind.again thx 4 ur reply

edited by moderator: Deleted email address

Hi, You shouldn't post your email address.

Please edit your post (click the Notepad & pencil icon at top right of your post) and remove it.

I will send you an Excel workbook containing the example.

Thanks

Humar

PS If you ever need to send an e-mail address again, use the private message system available on this site.

Hi Humar

I am sorry

i was not knowing that posting mail address is banned on this site, Please do the needful for my problem

i have not solved it yet.

Hi, I have e-mailed you an Excel workbook with the drop-down formula.

Regards

Ask Your Question

Weekly Poll