How to put formula in Data Validation List Excel?

0
votes

I am creating an excel sheet with following Data Validation drop down list.

  1. NA
  2. Done
  3. (add some formula here)

Basically, i will be able to select either plain text "NA"/ "Done" from the dropdown list. But sometimes, I want the user to be able to calculate some values based on the cell respective to the row selected so, I want to have one formula as a choice inside the data validation dropdown list. Is this possible?

Data Validation List Source

enter image description here

When I click on Formulae option, it should execute the formula with respect to the cells in that Row

enter image description here

But currently, the formula that i put in doesn't execute, instead it will just show the whole formula in the cell when activated.

enter image description here

1)How can i make it so that when i select the formula from data validation list, it will execute it instead of filling up the cell with it?

2)How do i set the formula so that it will be using the cell from the current Row? (for example, if i am using the data validation List in N60, the formula should adapt itself to use the cell (let's say A60?).

3
listexcelvalidationexcel-formulavba
what have you tried? A quick google search found a couple different way to do this – Pomul
@Pomul it's easy to google when you know what you are looking for – Chris Aung
For the answer to #2, I cant tell in your original formula but if you are in cell N60 >$A2 or -$A2 should work. A2 would be the first cell that you have data in that column (so it will ignore A1, your column title). Since you told it to stay in A with $, it should adapt the row number. Hope this helps. – jessica

3 Answers

1
votes

I may not be able to help with the second part, but I was seeking an answer to the first and discovered a solution/workaround using Name Manager.

First, in Formula > Name Manager, create a new reference (the "refers to" will contain whatever formula you are wishing to ultimately display in the validation list. For this example, we use the formula reference "=IF($H54=..." and Name it "UniqueName"

Now, we go into Data Validation, Select List, and input the three items we want displayed in the list, with an equals sign preceding our newly named reference: ie. "NA,Done,=UniqueName"

Note: You can't start with the =UniqueName or validation will try to read it all as a formula and fail.

This method will allow the user to display "NA", "Done", or "=UniqueName" in the cell; if "=UniqueName" is selected, the cell itself will interpret this as a formula and execute it accordingly, displaying the results of "=IF($H54=...", or whateverelse you have designated to use as a named formula.

If it's too late for yours, I hope this helps someone else who may face a similar problem.

0
votes

While I think I know what you're trying to say. Why don't you just use an IF formula to evaluate everything instead of selecting a drop down for every row manually. You already had it partially solved using IF. Just need to add the criteria for a "Done" and an "NA" 

=if(A1="date","Done",if(A1<"date","NA",if(something else until you have all your catergories))
0
votes

Just going to piggyback off of Mark's response.

If you really needed your named formula to be the first selection in the list, you can setup your list with a leading comma like so:

,=UniqueName,NA,Done

That worked out for my use, and there was no null item listed in the Data Validation drop down. Hope that helps!