return the value from the row above when conditions are met
Where the level of the row is 1, I want to return the value from the tasks column, but where the level of the row is not 1 (ie: child row), i want to return the value from the cell directly above. This way the children and parent have the same value. I dont know how to reference the cell above….
Best Answer
-
Ok. So you want to pull from a certain level and not necessarily the row immediately above. Try this:
=IF(COUNT(ANCESTORS(Tasks@row)) > 0, IFERROR(INDEX(ANCESTORS(Tasks@row), 2), Tasks@row))
Answers
-
Are you able to provide a screenshot for context?
-
Ok. so what I am trting to achieve is, in the phase column:
IF(Level@row=1,tasks@row, otherwise return the value of the cell above) -
Just to make sure… Do you mean the cell above, or do you mean the parent row? What should be on the row with "activity 2"?
-
I am wanting the cell above. I am wanting a column formula. The number of rows in the section will vary and there is the potential for the number of levels to vary as people configure for their specific need. As the level 1 row will pick up the phase name, I want that value to be duplicated through all rows in that phase.
-
I'm not sure I follow. Can you provide a screenshot with the rows manually populated to match what you are wanting to accomplish with a formula?
-
in the phase column, the red row and the rows below it would say Analysis. The yellow row and the rows below it would say Develop.
The red (and yellow) will populate from the first part of the formula if(level@row = 1, Phase@row
The issue is that there are not consistent number of rows or parent/child relationship in the rows between the red and yellow
-
Ok. So you want to pull from a certain level and not necessarily the row immediately above. Try this:
=IF(COUNT(ANCESTORS(Tasks@row)) > 0, IFERROR(INDEX(ANCESTORS(Tasks@row), 2), Tasks@row))
-
Thanks Paul, that worked. should I need it, is there a way to reference the cell above?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!