Data Validation and If Statements

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
 
K

Kevin Smith

Hello,

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

Newbeetle

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
 
K

Kevin Smith

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

Ivor Davies

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