Data Validation and If Statements

  • Thread starter Thread starter Newbeetle
  • Start date Start date
N

Newbeetle

Hi,

I have a simple IF statement that looks at A1 to return a number in B1, the
IF statement looks for a word, and return the aloocated number.

This works fine, but I want to use Data Validation in A1 so only the words
I have listed can be selected.

When I use data validation to pick the word, the IF statement then does not
work, so was wondering how I can get around this?

Hope that makes sence.

Newbeetle
 
Hello,

I have just tried this and it worked fine for me. What is the IF statemant
that you are using?
 
Hi Kevin,

The IF statement is

=IF(A1="Yes",100,IF(A1="No",0,""))

Data validation is "Allow List" and I linked the source to three cells that
have Yes, No and the third one is blank.

If I select No from the list I get 0 as expected, if I select the blank I
get a blank cell as expected but when I select Yes I get a blank as well.

Works fine if I remove the data validation and type Yes or No.

Regards

Newbeetle
 
Everything that i try is working correctly so i do not know what to suggest.

one thing to check would be that there are no spaces in the cells that you
are using for the data validation.
 
I've just tested this as well and it also works fine for me.

The only thing I can think of is that your calculation may be set to manual
so the formula isn't updating, check your calculation is set to automatic
(Tools>Options>Calculation>Automatic). Do you get an error in the adjacent
cell that applies the value based on the YES/NO value?

Otherwise it might be a formatting issue with the YES or NO source list, or
a formatting issue with the cells that contain the validated data.

A bit more information on the error you are getting would be helpful.
 
Back
Top