How can I add an index-collect formula using parent / child relationships?

Andrew Ryback

Hi everyone,

I'm looking for some help with a parent / child formula.

I've got a primary column that lists a project name (parent), with labor resources nested underneath (children). I also have two date columns in the same sheet - start date and end date. Both of these columns auto-populate with an index(collect formula, which is based on the project name (my parent).

Is there a formula that would auto-populate the start and end dates based on the start / end date entered into the parent row?

I'm struggling to find a way to mirror the index(collect formula in the children rows since I don't have a unique project name listed in those rows.

Thanks in advance.

Courtney S.

I might be misunderstanding, but if you simply want all the Child row start/end date cells to match the Parent row's start/end date answers, you can use the very simple "=PARENT()" for the child cells. To make the Start/End Date formulas into a column formula that will work differently for parent vs. child rows, you'll want to wrap the parent-specific and child-specific formulas inside of an IF function to check if the row is a parent or child. For example: =IF(COUNT(CHILDREN([Primary Column]@row)) > 0, insert your Index+Collect function here, PARENT())

Andrew Ryback

Thanks for this, @Courtney S.. The "=PARENT()" was embarrassingly simple.

However, I think I need something a bit more detailed as I'd like to convert this to a column formula.

The formula below is technically producing the right data; however, it's telling me I can't convert this to a column formula because the "formula syntax isn't quite right."

=INDEX(COLLECT({2024 PRODUCTION CALENDAR - Start Date}, {2024 PRODUCTION CALENDAR Range 2}, PARENT(NAME210)), 1)

Courtney S.

Could you give me the entire formulas you're trying to turn into column formulas in your start & end date columns?

One thing I'm noticing is that your PARENT function seems to be specifying a specific cell and that might be the problem.

Andrew Ryback

This is the full formula:

=INDEX(COLLECT({2024 PRODUCTION CALENDAR - Start Date}, {2024 PRODUCTION CALENDAR Range 2}, PARENT(NAME210)), 1)

…and you're right about the parent column. I tried keeping the parent column blank - "PARENT()" - but that also spit back an error.

Courtney S.

Hi @Andrew Ryback sorry about the delayed response, I missed your reply until now. If you're trying to have the Child rows simply match their parent row's dates, you do not need to use the Index/Collect formula at all for those rows. That's why I mentioned the IF function. So, if your Parent row formula is: "=INDEX(COLLECT({2024 PRODUCTION CALENDAR - Start Date}, {2024 PRODUCTION CALENDAR Range 2}), 1)"

Then to make a column formula that works for both the parent and child rows, you put the differing formulas inside of an IF formula to check if the row is a parent or child. For example: =IF(COUNT(CHILDREN([Primary Column]@row)) > 0, INDEX(COLLECT({2024 PRODUCTION CALENDAR - Start Date}, {2024 PRODUCTION CALENDAR Range 2}), 1), PARENT())

=IF(COUNT(CHILDREN([Primary Column]@row)) > 0, —— If the count of the children rows is more than zero (ie. it's a parent row)…

INDEX(COLLECT({2024 PRODUCTION CALENDAR - Start Date}, {2024 PRODUCTION CALENDAR Range 2}), 1), —— …use the parent row formula….

PARENT()) —— …otherwise (ie. for Child rows), just look at the parent row and copy the value.