Repeating a Formula referencing children rows

Jeana

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

Paul Newcome

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"))

Paul Newcome

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.

Jeana

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

Paul Newcome

I'm not sure I follow...

Jeana

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!!

Paul Newcome

Will it always be the first, second, and last that you reference?

Jeana

Yes, it will.

Paul Newcome

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"))

Jeana

Sweet!!! The first one works fine. THANKS!

Paul Newcome

Happy to help! 👍️