Formula Help - I have two children under each parent. Need the Status to Change if they match

Breelyn Brown

Hello,

My sheet takes into account 1 store. There are two children line where I will datamesh data under the Fueling Position 1 column. I need the parent to show Green if they match with the same number and red if they do not match. I can't get a formula to do this. Thanks in advance for your help!

jessica.smith

@Breelyn Brown

Here is one suggestion:

Add three helper columns that can be locked and hidden from view.

This first is a "Level" column that counts ancestors to determine where in the parent/child hierarchy the row falls. This will be used to differentiate between the Parent store and the children lines when setting up conditional formatting later. Formula: =COUNT(ANCESTORS())

The second is a column ("Match Parent" in screenshot below) that will determine if each child row contains the store number. If yes, it will be checked, if no it will be left blank. Formula:

=IF(COUNT(ANCESTORS()) > 0, IF(OR(CONTAINS(PARENT([Store Number]@row), [Store Number]@row), HAS([Store Number]@row, PARENT([Store Number]@row))), 1, 0), 0)

The third column ("Match Children" in the screen shot) will count the number of children and compare it to the number of children that contain the parent store number. If they are equal, then all children lines contain the store number. If they are not equal, then at least one child has an unmatched store value. Formula:

=IF(COUNT(ANCESTORS()) = 0, IF(COUNTIF(CHILDREN([Match Parent]@row), 1) = COUNT(CHILDREN([Store Number]@row)), 1, 0), 0)

Then set up two conditional formatting rules to turn the Parent Store number red or green based on whether the "Match Children" checkbox is engaged or not for the parent row only.

Breelyn Brown

Thank you for responding. This sheet will have columns from Fueling Position 1 to Fueling Position 29. So I am looking for a formula to match on the parent row with no helpers. Space will be an issue.