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
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!