Repeating a Formula referencing children rows
So I have a sheet where the parent row is job requested (Nebraska YR 3). On this row I need a formula to display the current status of the children (TASK)for each request (STATUS), based on a date field not being blank (DATE COMPLETED). I have created a formula that does this but when a new request is added to the sheet I'd like it to automatically generate the children (TASK) and the related STATUS formula?
Each request has the same tasks. Right now I'm having to manually add the Children (TASK) for each new request. The request are generated by a form and then added to this sheet.
Is this possible?
Thanks for any help you can offer!
Jeana
Best Answer
-
Try something like this if it is always the same number of children and you are referencing child 1, 2, and 9...
=IF(ISDATE(INDEX(CHILDREN([Date Completed]@row), 9)), "Job Completed", IF(ISDATE(INDEX(CHILDREN([Date Completed]@row), 2)), "In Progress", "Not Started"))
If it is not always the same number of children and you are referencing child 1, 2, and n...
=IF(ISDATE(INDEX(CHILDREN([Date Completed]@row), COUNT(CHILDREN([Task Name]@row)))), "Job Completed", IF(ISDATE(INDEX(CHILDREN([Date Completed]@row), 2)), "In Progress", "Not Started"))
Answers
-
You will have to do this manually, as each parent row is added to the sheet, but you can use copy/paste on entire sections at a time and formulas should pull through.
-
Bummer. Is there any way you know of the automate the reference of the STATUS formula to the new children rows? I figure not but it doesn't hurt to ask right?!
Thanks
-
I'm not sure I follow...
-
Pics are worth a thousand words! :-)
I'm pulling in a row from another sheet using Form and Automation. The sheet above is the destination sheet and it tracks the asks. In the Parent Row (pulled in from another sheet) I have a formula in one cell that looks at the children and determines if the status of the tasks (in general - Job Completed, In Progress, Not Started).
Since this formula depends on the status of the children, which I currently have to manually add when a new Parent row is pulled in, how can I get the formula to automatically look at their own Children? When a new row is pulled it it's looking at the children from the tasks above. The steps are exactly the same for each Parent request.
Maybe I'm going about it wrong?
Thanks for any suggestions you might have!!
-
Will it always be the first, second, and last that you reference?
-
Yes, it will.
-
Try something like this if it is always the same number of children and you are referencing child 1, 2, and 9...
=IF(ISDATE(INDEX(CHILDREN([Date Completed]@row), 9)), "Job Completed", IF(ISDATE(INDEX(CHILDREN([Date Completed]@row), 2)), "In Progress", "Not Started"))
If it is not always the same number of children and you are referencing child 1, 2, and n...
=IF(ISDATE(INDEX(CHILDREN([Date Completed]@row), COUNT(CHILDREN([Task Name]@row)))), "Job Completed", IF(ISDATE(INDEX(CHILDREN([Date Completed]@row), 2)), "In Progress", "Not Started"))
-
Sweet!!! The first one works fine. THANKS!
-
Happy to help! 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives