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

Options
Andrew Ryback
Andrew Ryback ✭✭
edited 05/13/24 in Formulas and Functions

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.

Answers

  • Courtney S.
    Courtney S. ✭✭✭✭✭
    Options

    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
    Options

    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.
    Courtney S. ✭✭✭✭✭
    Options

    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
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!