-1

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?

Improve this question
10
  • 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? Commented Nov 23, 2014 at 14:37
  • I added data validations in A6 cell: Allow->List, Source->MIN,MAX,SUM Commented Nov 23, 2014 at 14:40
  • may be it can possible in INDIRECT or OFFSET function. Commented Nov 23, 2014 at 14:47
  • what is the purpose i=of using DATAVALIDATION list? You could have used only SUM function. Commented Nov 23, 2014 at 14:48
  • I added it because I want to calculate different function value in same cell. Commented Nov 23, 2014 at 14:53

1 Answer 1

Reset to default
3
+50

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".

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.