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

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.

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.