# Based on value on a child sub type YES / NO

1. ## 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.

2. ## 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. ## 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. ## 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. ## 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.

6. ## 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.

7. ## 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. ## 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. ## 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. ## 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. ## Re: Based on value on a child sub type YES / NO

Glad we could help. Thanks for the rep!

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

#### 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