Auto populating Status of a child row to duplicate Parent Row
I have a project I'm working on where I want to auto generate steps based on the status in two columns. I figured out the formula for that.
My issue right now is that without me manually entering/copying the status (I will normally not be in this sheet after it's developed) or having the user enter the status for all 6 rows for the same account (there will be 5-10 accounts on a sheet), I cannot figure out how to get a status in the children rows for my formulas to use as reference.
The formula I'm using is =INDEX(COLLECT({Auto Populate Action Steps Range 4}, {Auto Populate Action Steps Range 2}, [Child Customer Status]@row, {Auto Populate Action Steps Range 3}, Customer@row), 1) so I need something in the "Child Customer Status" column.
I've included a copy of my sheet and the sheet that is referenced in the formula.
The invalid value is only there because the child customer status column is blank. I manually entered a few in the top 5 boxes to make sure the formula worked.
Answers
-
Did you want them to stay blank if nothing is entered?
If so you can just add an iferror statement to your formula.
=IFERROR(INDEX(COLLECT({Auto Populate Action Steps Range 4}, {Auto Populate Action Steps Range 2}, [Child Customer Status]@row, {Auto Populate Action Steps Range 3}, Customer@row), 1),"")
-
I'm not too worried about the second formula to populate the action leaving a blank if there's nothing to fill as they will be entering an account when needed and they need the steps as soon as they enter it.
I'm more addressing the need to take the "existing", "developing", etc status from the parent row down to the children rows. Although, i think it might be children rows down to grandchildren rows.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!