Results 1 to 9 of 9

Thread: Need Help

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Need Help

    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.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,298
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Need Help

    When you say P2 has "No Value" is that text in the cell or is the cell "" (ie empty)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help

    Currently it shows No Value but I can have excel clear those No Value out and have it blank.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,298
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Need Help

    Ok, how about
    =--AND(OR(I2={"Returns","Delivery"}),P2<>"No Value")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help

    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.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,298
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Need Help

    Glad to help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help

    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)

  8. #8
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help

    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.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,298
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Need Help

    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)))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •