If/then statements with OR statements

I have a spreadsheet with two columns that have "Yes" or "No" as choices. I would like a third column to use a formula to display the results:

IF C="Yes", "Priority"
and ALSO
If D-"Yes", Priority"

IF neither C or D is "Yes", then I want the result displayed as "Not Priority".

This seems simple enough, but I can't get it to work.

I tried the formula:

=IF(C6="Yes", "Priority", IF(D6="Yes", "Priority", "Not Priority"))

and this returns "Priority" in column E (the results column) ONLY if column D is "Yes" already-- if column D is "No," it doesn't matter what the person selects in column C- it still says "Not Priority" in column E.

Thanks for your help!

Hi

Try

For priority as a result do you want both C6 & D6 to say Yes, or any one if say yes you want priority. If any one than put OR in place of AND.

=IF(AND(C6="Yes",D6="Yes"),"Priority","Non-Priority")

Thanks for your quick reply.

To clarify: I would like "Priority" displayed if either C6= "Yes" OR if D6="Yes." I will try your suggestion

Thanks again!!!

Replace AND with OR in the function.

This is still not working. I tried using "OR" in place of "and", Was this the wrong thing to do? thanks

It's working on my end. May be you should post your file.

Yep, formulas posted with OR will do what you're asking.
If it's NOT, then maybe the values in C6 or D6 don't = Yes EXACTLY.
Check them for extra spaces like " Yes" or "Yes "

If that doesn't help, it's time to post a sample file.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

or

=IF(COUNTIF(C6:D6,"*Yes*"),"Priority","Not Priority")

Okay, attached is a sample of my spreadsheet with fake data put in. The "Prioritize" tab is where the formula is. One other thing I would like to change is not have the combo box also show a number "1" or "2" in the box. Originally, this data was scores- so if the client's score plus the backend (fixed data score from backend tab) equalled 6 or greater, the item was labelled "priority". This is why the "1" and 2" show up- originally the combo box had choices for "1," "2" and "3", I thought I changed this on the "backend 2" tab. Now we have decided to change the scoring to reflect-If the client says "Yes" OR if we say "Yes," both of those would be displayed as a priority. Also, if we label something as priority (Column D), then it is displayed in the results as a priority no matter what the client's answer is. I hope that makes sense.

Thanks again

I posted my file (see bottom of thread)- I still can't get it to work. I did give reputation to all those who provided replies :-)

Your Yes or No options appear to be in Columns D and E, NOT C and D as the formulas have been written for.

In simple language, can you tell the result for these situations:

Case 1: C6 = Yes D6 = Yes
Case 2: C6 = No D6 = Yes
Case 3: C6 = Yes D6 = No
Case 4: C6 = No D6 = No

Can you tell what result you expect in these situations? And I did not got my rep

Oh my! Not sure how I didn't catch that- perhaps it happened in my creation of a fake data example (the animals).

OK so the formula works now-

So the next question is, how do I get the combo box in Column C--which has choices of "Yes" and "No"-- to not display numbers below the combo box when you select "yes" or "no"?

I only want the "yes" or "no" to be selected, and no numbers whatsoever in column C (or anywhere else).

you will see that I manipulated the formulas in column "E" to make the numbers displayed as "yes" or "no", because i couldn't figure out how to get the numbers to go away

Cases 1, 2, and 3 would all display "Priority". Case 4 would display "Not Priority" -thanks- and i gave you your rep :-)

Try making it an ActiveX combobox, isntead of a UserForm combobox

I am new to combo boxes. (I had much help on this spreadsheet)- can you explain how I would do this? And if I do this Active X Combo box, do I still need the "Backend data" tab that lists "yes" or "no" as the options for the drop down menu in the combo box? Thanks :-)

Why don't you use Data Validation drop down instead of a combobox? That way you can show Yes / No and the formula can be derived from that.

Can you please explain to me the steps involved with a data validation drop down? Would there still be a drop down menu where the user selects "yes" or "no" that looks like the drop down menu in the current version? Thanks.

The ActiveX combobox is very similar to the forms combobox

When you click Insert on the Developers tab, there's 2 sections of items, one for forms, one for activex
Just choose the combobox from the ActiveX section.
Then you can Right Click on it, choose Properties
In the box that pops up, fill in the LinkedCell and ListFillRange properties with C4 and ClientScore

Yes you still need the source data for the ClientScore List.

IThanks Jonmo1. Is there a way to edit the combo box that already exists, or do I need to delete the current combo box and start over?

Thanks

I apologize, but I do not really understand your reply, TMS

I am not sure why I am using both. I tried many things to make this work, and all of those things were new to me. I am very confused right now.

Originally Posted by tnovak

IThanks Jonmo1. Is there a way to edit the combo box that already exists

No, you would need to delete the existing one, and create a new one.

But I agree with TMS
You already have Data Validation setup on the cells, but they are giving 1 2 3 results.
You can just change that to Yes No ""

Hi TMS,

Unfortunately, when I tried to open your file it said Active X is not supported by Excel for Mac 2011. how else could I do this? Thanks

Jonmo1- I cannot figure out how to change the 1, 2, 3 to Yes, No. I tried typing "Yes, No" into the data validation editing section where it said "source," and that did not work

Just type

Yes, No

No quotes

I am sorry, it is still not working. Somehow it is working in one cell, but no matter how many times I change the settings in the other cells, the numbers 1, 2 and 3 are still displayed instead of yes and no. It only works in the first cell

Actually, the "yes" or "no" does display when you select the drop down menu in the bottom right of the box (the data validation menu). but when you select the combo box drop down menu "yes" or "no", the data validation info in the bottom right corner does not change. I only want the user to have to select "yes" or "no" once, and "yes" or "no" to remain displayed in the box where the combo box is. Ideally, I think there would ONLY be a combo box. Or ONLY one version of a drop down menu, and NOT the data validation way- at least not if that way is only displayed in the bottom right. I wanted the choices to be easy for the user to find. I hope that makes sense

Originally Posted by tnovak

Actually, the "yes" or "no" does display when you select the drop down menu in the bottom right of the box (the data validation menu). but when you select the combo box drop down menu "yes" or "no", the data validation info in the bottom right corner does not change.

Pick one or the other.
Data Validation OR ComboBox

If you're going with Data Validation, remove the comboboxes
If you're going with Comboboxes, remove the Data Validation.


Personally, I'd stick with the Data Validation, much easier to work with.

Thank you for your efforts so far. There are still some bugs:

1.) everything from C9 down in the C column still displays numbers in the bottom right corner.

2.) Why are there three "priority" columns now?

3.) Now, when the backend data says "yes" and the client data says "no," The third "priority" column (again not sure why it is there?) still shows "not priority", and it should show "priority if EITHER client data OR backend data say "yes" for priority. I hope that makes sense

Thank you Jon Mo. So my question still remains about the data validation: if i ONLY have data validation, can I have a drop down menu just like the combo box looks in appearance? I want the drop down menu to be front and center for the client to see right away, not in the bottom right the way data validation is. I want that box to show up all the time, not only when the person clicks on the cell, which was my issue with data validation to begin with

Thanks

So unfortunately this issue still has not been solved.

See the following regarding TMS 's reply: Thank you for your efforts so far. There are still some bugs:

1.) everything from C9 down in the C column still displays numbers in the bottom right corner.

2.) Why are there three "priority" columns now?

3.) Now, when the backend data says "yes" and the client data says "no," The third "priority" column (again not sure why it is there?) still shows "not priority", and it should show "priority if EITHER client data OR backend data say "yes" for priority. I hope that makes sense

and the following regarding JonMo1's reply:

Thank you Jon Mo. So my question still remains about the data validation: if i ONLY have data validation, can I have a drop down menu just like the combo box looks in appearance? I want the drop down menu to be front and center for the client to see right away, not in the bottom right the way data validation is. I want that box to show up all the time, not only when the person clicks on the cell, which was my issue with data validation to begin with

Thanks

No, data validation only looks the way you see it.

Sounds like a design choice for you to make, then for visual sake the combobox is the better solution.
And since you can't use ActiveX on the Mac Version...
The way you had it originally is the simplest solution.
And remove the data validation, just remove confusion so there is only 1 input method.

Then use a formula to convert the 1 2 3 in columnC to Yes or No in Column E

And make your formula in column F refer to D and E instead of C and D.

Thank you. I did get the data validation to go away. However, there is still a "1" or "2" displayed in column C at the bottom right after you select "yes" or "no". How do I make this go away? Thanks

Originally Posted by tnovak

However, there is still a "1" or "2" displayed in column C at the bottom right after you select "yes" or "no". How do I make this go away? Thanks

Format the cell so that the Font color is the same as the Background color.
Technically, the number is still there but you just can't see it.

JonMo- brilliant idea about the font/fill color formatting! It looks great now :-) there are a few other things I want to do with this, but I'll post them in a new thread when I'm ready to ask the Qs. Thanks to ALL OF YOU for your help and your patience with my novice status with many of these steps!

You're welcome.