Change value of child based on parent

NitaP

In this sheet I'm monitoring the inventory of raw materials. Currently, each child row compares available stock to low stock amount and returns OK or "low". However, the parent includes the total of all batches of the raw material, so that even if one batch is low, the quantity of the raw material might be sufficient. So, if the parent Stock is Low cell shows "OK", I'd like the child cells in that column to show "OK".

What do I need to change to accomplish this? Also, is there a better way of setting up the functions I currently have?

Currently:

In parent Stock Status cell:

=IF(Available@row > [Low Stock Amount Calculated]@row, "In Stock", "Reorder")

In parent Stock is Low cell:

=IF(Available@row < [Low Stock Amount Calculated]@row, "Low", IF([Stock Status]@row = "Expired", "Low", IF([Stock Status]@row = "Do Not Reorder", "Do Not Reorder", "OK")))

In child Stock Status cell:

=IF([Low Stock Amount Calculated]@row = 0, "Do Not Reorder", IF([Best Before Date]@row < TODAY(), "Expired", IF(Available@row < [Low Stock Amount Calculated]@row, "Reorder", IF(AND(Available@row < [Low Stock Amount Calculated]@row, [Best Before Date]@row > TODAY(), [Best Before Date]@row < TODAY(+20)), "Reorder", "In Stock"))))

In child Stock is Low cell:

=IF(Available@row < [Low Stock Amount Calculated]@row, "Low", IF([Stock Status]@row = "Expired", "Low", IF([Stock Status]@row = "Do Not Reorder", "Do Not Reorder", "OK")))

Genevieve P.

Hi @NitaP

Thank you for clarifying that you're putting the formula in a different column! Yes, in this case we will need to reference the column that has the children, like so:

=IF(COUNTIF(CHILDREN([Child Date Status]@row), "Expiring") > 0, "Expiring", IF(Available@row > [Low Stock Amount Calculated]@row, "In Stock", "Reorder"))

Cheers,

Genevieve

Lucas Rayala

I think I would split your Stock is Low into a Parent and Child column and use a third reference column to indicate if the row is a Parent or Child for reference. That could read:

Column name: "Parent"

=IF(SUM(CHILDREN())>0, "Yes", "No")

For your Parent Stock is Low column, add an IF statement to blank out the non-relevant values, so:

Column name: "Parent Stock is Low"

=IF(Parent@row="No", "", IF(Available@row < [Low Stock Amount Calculated]@row, "Low", IF([Stock Status]@row = "Expired", "Low", IF([Stock Status]@row = "Do Not Reorder", "Do Not Reorder", "OK"))))

For your Child Stock is Low column, add a similar IF statement, but also add a lookup to the parent of the Parent Stock is Low column and put "OK" if the parent is "OK".

Column name: "Child Stock is Low"

=IF(Parent@row="No", "", IF(PARENT([Parent Stock is Low]@row)="OK", "OK", IF(Available@row < [Low Stock Amount Calculated]@row, "Low", IF([Stock Status]@row = "Expired", "Low", IF([Stock Status]@row = "Do Not Reorder", "Do Not Reorder", "OK")))))

I think you need the separate columns to avoid self-referencing.

NitaP

I appreciate your help. I put in place what you suggested and then realised it was more than I needed. I actually deleted the Stock is Low column and for the Stock Status Column I now have this:

Parent rows:

=IF(Available@row > [Low Stock Amount Calculated]@row, "In Stock", "Reorder")

Children rows:

=IF([Best Before Date]@row < TODAY(), "Expired", IF(Available@row = 0, "OOS", IF(AND([Best Before Date]@row > TODAY(), [Best Before Date]@row < TODAY(+30)), "Expiring", IF(AND(Available@row < [Low Stock Amount Calculated]@row, Available@row > 0), "Low", "OK"))))

This is working fairly well:

However, I'd like to adjust the parent Stock Status to show "Expiring" if one of the child rows shows "Expiring" and I can't get it to work. Ideas?

Genevieve P.

Hi @NitaP

You can add an IF statement on to your Parent formula to COUNT if any of the Children say "Expiring", like so:

=IF(COUNTIF(CHILDREN(), "Expiring") > 0, "Expiring", IF(Available@row > [Low Stock Amount Calculated]@row, "In Stock", "Reorder"))

Cheers,

Genevieve

NitaP

It didn't work. Do I need to reference the fact that I'm looking at the children in a different column?

Genevieve P.

Hi @NitaP

Thank you for clarifying that you're putting the formula in a different column! Yes, in this case we will need to reference the column that has the children, like so:

=IF(COUNTIF(CHILDREN([Child Date Status]@row), "Expiring") > 0, "Expiring", IF(Available@row > [Low Stock Amount Calculated]@row, "In Stock", "Reorder"))

Cheers,

Genevieve

NitaP

Thank you!