If function to test drop down menu selection

[Kanaka360]

I have what is, I hope, a simple question. But I can't seem to find the answer on the interwebs, so here I am. I am developing a spreadsheet at work that is very similar to one that I made at a previous job. I am using the same formula in each spread sheet, but the one I am developing now does not work, where the previous one works perfectly.

I have a drop down menu created with data validation. Each possible selection is one or two letters of text, or the selection can be left blank. The drop down is cell A12, and an example of the if function that is used many times in this spreadsheet is as follows:

=IF($A$12="L",1,0)

In the previous spreadsheet, the function returns true only when the appropriate selection is made. In the latest version, it will return true if the appropriate selection is made, or if drop down selection is blank. I have been able to work around this with the following:

=IF($A$12="",0,IF($A$12="L",1,0))

It works, but is annoying, anybody know what I could be doing wrong here?

[Punnam]

Hi,

Plz provide a sample book need to check the drop down , i.e data validation also as the if function is working fine .

Punnam

[FDibbins]

Hi and welcome to the forum

=IF($A$12="L",1,0)

In the previous spreadsheet, the function returns true only when the appropriate selection is made

This should still be the case. I cannot see why that will return 0 (FALSE), if you have anything other than "L" in A12.

Can you upload a small sample workbook for me to look at please?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[sktneer]

One reason may be that your drop down list may contain leading or trailing spaces. Check the length of A12 and make sure it is 1.
Or alternatively you can try

Please Login or Register  to view this content.

[Kanaka360]

Here are the two files, at least I think they're attached anyhow. I've highlighted the cells that are related to this formula. Let me know if you have any questions. I tried adding the trim function, and that didn't do it. Let me know if you can figure this one out. Like I was saying, I found a work around, but I'm just curious what I'm doing wrong here.

Thanks

[sktneer]

On both of your sheets, the formula is working fine. I don't see any issue with the formula.
Make sure that Calculation Options is set to Automatic to see if this is the issue.

[Kanaka360]

Please take another look. I've uploaded these again with some values filled in. When no option is selected in the yellow cell, the green cell returns 0 in the "old" spreadsheet, but returns 1 in the "CTA" spreadsheet (at least on my machine).

Calculation is set to automatic in both.