| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation and If Statements
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data Validation and If Statements
Hello,
I have just tried this and it worked fine for me. What is the IF statemant that you are using? -- Kevin Smith :o) "Newbeetle" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data Validation and If Statements
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 "Kevin Smith" wrote: Hello, I have just tried this and it worked fine for me. What is the IF statemant that you are using? -- Kevin Smith :o) "Newbeetle" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data Validation and If Statements
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. -- Kevin Smith :o) "Newbeetle" wrote: 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 "Kevin Smith" wrote: Hello, I have just tried this and it worked fine for me. What is the IF statemant that you are using? -- Kevin Smith :o) "Newbeetle" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data Validation and If Statements
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 (ToolsOptionsCalculationAutomatic). 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. "Kevin Smith" wrote: 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. -- Kevin Smith :o) "Newbeetle" wrote: 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 "Kevin Smith" wrote: Hello, I have just tried this and it worked fine for me. What is the IF statemant that you are using? -- Kevin Smith :o) "Newbeetle" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If statements in Data Validation | Excel Worksheet Functions | |||
| Data Validation - IF statements | New Users to Excel | |||
| 14 Nested IF statements creating different validation lists | Excel Worksheet Functions | |||
| If statements with validation lists | Excel Worksheet Functions | |||
| Data Validation w/ If, Match & Index Statements | Excel Worksheet Functions | |||
Hybrid Mode
