Need Help

Techipubs

New Member
Joined
Sep 20, 2019
Messages
6
I am quite new to Excel and I am currently using Excel 2013 at work. What I am in need of is probably extremely simple for most people on this forum so I am hopeful that I can get some help with this.

What I need:

Use of IF, AND, OR formula

I have data in Column I which has 5 different items. I also have data in Column P which either has (No Value) or (Part #) the part # can be anything.
I need is a formula that I would put into Column H2 that will do the following:

Check if data in Cell I2 = "Delivery" or "Returns" if so, check if data in cell P2 = "No Value" or "" and if data in P2 = "No Value" place a 0 in cell H2 but if P2 has anything other than "No Value" return 1 in cell H2

Any help is greatly appreciated. This has taught me that I need to do way more training in Excel to be better at my job.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
When you say P2 has "No Value" is that text in the cell or is the cell "" (ie empty)
 
Upvote 0
Ok, how about
=--AND(OR(I2={"Returns","Delivery"}),P2<>"No Value")
 
Upvote 0
Thank you Fluff! I racked my brain all day yesterday and most of last night and everything that I have read and watched to teach myself how to write this formula didn't look anything like the formula that you gave to me.

Again, I greatly appreciate your quick response and assistance.
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
Hello again. One last question if you don't mind.

How would I nest the following 4 formulas into 1 cell to return a 0 if False and 1 if True:

=--AND(OR(M2={"Returns","Delivery","Archived - Claims - Returns"}),U2<>"")
=--AND(OR(M2={"Archived - Part","Part Information"}),Q2<>"",R2<>"")
=--AND(OR(M2={"Archived - Order","Order"}),V2<>"")
=IF(OR(I2=1,J2=1,K2=1),1,0)
 
Upvote 0
Actually the last line would not be needed in the formula I believe since it is checking the other three columns before returning a 0 or 1 into the target cell.

=--AND(OR(M2={"Returns","Delivery","Archived - Claims - Returns"}),U2<>"")
=--AND(OR(M2={"Archived - Part","Part Information"}),Q2<>"",R2<>"")
=--AND(OR(M2={"Archived - Order","Order"}),V2<>"")

I tried this formula:
=--AND(OR(H3={"Returns","Delivery"}),Q3<>"",--AND(OR(H3={"Part Information"}),L3<>"",M3<>"",--AND(OR(H3={"Order"}),R3<>"")))

But of course it is only returning 0's when I know there should be some 1's in there when I manually check the cells.
 
Upvote 0
How about
=IF(AND(OR(M2={"Returns","Delivery","Archived - Claims - Returns"}),U2<>""),1,IF(AND(OR(M2={"Archived - Part","Part Information"}),Q2<>"",R2<>""),1,IF(AND(OR(M2={"Archived - Order","Order"}),V2<>""),1,0)))
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top