How to write and run excel function in data validation ms-excel

Question

I have an Excel sheet as below-

Cell No / Column A
-------------------
1.    Total
2.    $1,350.00
3.    $2,670.00
4.    $2,891.00
5.    $1,107.00

6.    Drop-Down List

I have added Data Validation List with some function name like MIN, MAX, SUM.I want to calculate the value of A2:A5 cell and display in A6 cell, if i select any of one function in A6 cell, which is added through data validations. Suppose I select SUM form in A6 cell, it will show sum of value function like SUM(A2:A5).

How can I do that?

Post what code you have tried. Also, listbox can serve your purpose as well. And, you want result in A6, while dropdown is on A6. How come? — ZAT, Nov 23 '14 at 14:37
I added data validations in A6 cell: Allow->List, Source->MIN,MAX,SUM — Bikash Mahata, Nov 23 '14 at 14:40
what is the purpose i=of using DATAVALIDATION list? You could have used only SUM function. — Paresh J, Nov 23 '14 at 14:48
I added it because I want to calculate different function value in same cell. — Bikash Mahata, Nov 23 '14 at 14:53
This is very easy if you use two cells instead of one cell. **A6** for the result and **A7** for the *DV* pull-down. — Gary's Student, Nov 23 '14 at 15:01
`=IF(A6="Min",MIN(A2:A5),IF(A6="Max",MAX(A2:A5),IF(A6="Sum",SUM(A2:A5),"")))` — Siddharth Rout, Nov 23 '14 at 15:03
You can put the formula anywhere except in the range `A2:A6` — Siddharth Rout, Nov 23 '14 at 15:03
I have already done it Mr. Siddharth, but i want to get result in same cell. — Bikash Mahata, Nov 23 '14 at 15:07
Read up of worksheet_Change. [here](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) is a start. Do that and we will take it from there. I just tested it and it works. But I will only post the code if you show some efforts. I will not give you the code in a platter :) — Siddharth Rout, Nov 23 '14 at 15:54

Marcel · Accepted Answer · 2014-11-23T16:02:00.147

You can simply create these three formulas in Column B, say, MIN in B1, MAX in B2, SUM in B3. Then pull your data validation drop down list from these cells which already contain that formula.
However you need to format your B1, B2, B3 cells so that in data validation list you see for example MIN, MAX or SUM. I have done this for you in this dropbox link

like this:
here is the preview

enter image description here
You can hide column B so that nobody can make any changes to it.
I have also selected the entire column range in column A in the formulas so if you add anything later to that column it automatically calculates them alL.
You can add as many formulas to column B as you want,and then put them in your data validation dropdown menu. Don't forget to format your cells in column B. You should custom format it and put your desired text inside "", like "MIN PRICE".

How to write and run excel function in data validation ms-excel

1 Answers1