Formula Help - I have two children under each parent. Need the Status to Change if they match
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!
Answers
-
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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!