+ Reply to Thread
Results 1 to 11 of 11

Based on value on a child sub type YES / NO

  1. #1
    Registered User
    Join Date
    10-21-2018
    Location
    florida, usa
    MS-Off Ver
    365
    Posts
    9

    Based on value on a child sub type YES / NO

    Hi,

    A little bit of background, I am a Production Planner and my planning is based on a worksheet which reads values for all inventories available. Most of my planning is to build KITS (which is a Finished Good SKU composed of many child SKUs)

    If I want to build a kit TK-101 I want to be able that on my working tab I can see if I am able to produce such KIT based on the child SKUs for that KIT.

    An example of my working tab looks:

    FINISHED GOOD PLAN YES/NO
    TK-101 1000 NO



    An example of my tab reading inventories:

    FINISHED GOOD COMPONENT DESCRIPTION WH10 WH13 WH16
    TK-101 BOX-151 POST TREATMENT TRIAL KIT BOX 405 0 0
    TK-101 CHILD1 COMPONENT1 1000 200 10000
    TK-101 CHILD2 COMPONENT2 0 200 10
    TK-101 CHILD3 COMPONENT3 20000 10000 50000
    TK-101 CHILD4 COMPONENT4 35000 55000 100
    TK-101 CHILD5 COMPONENT5 2000 0 0
    TK-101 CHILD6 COMPONENT6 13000 15000 5000
    TK-101 CHILD7 COMPONENT7 13000 12000 10000

    As you can see above, I would want to have visibility on my first tab (SHEET1) where I am planning my production. As of now, I have to go back from tab to tab to see if I have all inventories available on child SKUs.

    What I am looking for is that SHEET1 can write YES or NO if it reads on SHEET2 that one of other components is under a value. If <100 write NO; if >100 write YES

    Let me know if I am clear enough and if I need to upload a sample worksheet.

    Thanks for your time in reading this thread.

  2. #2
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    Excel 2013
    Posts
    782

    Re: Based on value on a child sub type YES / NO

    jairjacom,

    Not sure what the significance of WH10, WH13 and WH 16 are, but put your table above in Sheet 2, put "TK-101" into A2 of sheet 1, and this formula into B2 on Sheet 1.

    It will check whether any rows in sheet 2 with "TK-101" in Col A have less than 1000 in Col D (WH10). If it finds any, B2 it puts "No", and if there are more than 1000 of ALL the components it puts "Yes"

    =IF(COUNTIFS(Sheet2!A3:A9,A2,Sheet2!D3:D9,"<"&1000)>0,"No","Yes")

    If you want to check for WH13 and WH16 seperately, change the Col D to COl E or Col F.

    Ochimus

  3. #3
    Registered User
    Join Date
    10-21-2018
    Location
    florida, usa
    MS-Off Ver
    365
    Posts
    9

    Re: Based on value on a child sub type YES / NO

    Hi Ochimus,

    I tried and understand your formula (even though I am new with COUNTIFS formula). It is giving me an error: "to get around this, type an apostrophe (*) first

  4. #4
    Registered User
    Join Date
    10-21-2018
    Location
    florida, usa
    MS-Off Ver
    365
    Posts
    9

    Re: Based on value on a child sub type YES / NO

    I am sorry I cannot post my formula since I am getting submission errors in the forum. I am not allowed to post kind of links, images, etc. etc.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    7,860

    Re: Based on value on a child sub type YES / NO

    Hello jairjacom and Welcome to Excel Forum.
    It may help us to help you if you upload the workbook from which the data in post #1 is taken.
    To upload a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window. (The paperclip icon does not work.)
    We also need to know how to determine the number of components available as it appears the any (or all) of the columns labeled WH10, WH13 and/or WH16 could be involved.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    10-21-2018
    Location
    florida, usa
    MS-Off Ver
    365
    Posts
    9

    Re: Based on value on a child sub type YES / NO

    Thanks for your help on this.

    I have uploaded a sample sheet. For clarification, wh10/13/16 are buckets where inventories are kept in our system. We can disregard wh10 (not needed).

    All components to make a FINISHED GOOD sku are in wh13 and/or wh16. What I am looking for is to be able to know on my planning tab if all components are available based on a determined value (we can make this value <100 SAY NO >300 SAY YES)

    Basically, if I know I have to make a FG for TK-101 I want to see on the plan tab a YES/NO to populate so I don't have to go back and forth to the components tab.

    Hope this makes sense.
    Attached Files Attached Files

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,778

    Re: Based on value on a child sub type YES / NO

    If you want to return NO when <100 and YES when >300, what about 100 through 300?

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,778

    Re: Based on value on a child sub type YES / NO

    I'm assuming that you meant something more along the lines of <100 = NO and ≥100 = YES.

    I would make a helper column in the components worksheet with this formula in G2:
    =SUM(E2:F2)

    Then you can use this in plan!D2:
    =IF(COUNTIFS(components!A:A,C2,components!G:G,">=100"),"YES","NO")

  9. #9
    Registered User
    Join Date
    10-21-2018
    Location
    florida, usa
    MS-Off Ver
    365
    Posts
    9

    Re: Based on value on a child sub type YES / NO

    YES!!!!

    Formula and helper suggestions works wonders. I modified the formula to this:

    "<=200"), "NO", "YES")

    Thank you very much for your help guys!!

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    7,860

    Re: Based on value on a child sub type YES / NO

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

  11. #11
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,778

    Re: Based on value on a child sub type YES / NO

    Glad we could help. Thanks for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1