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.
Answers
-
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())
-
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)
-
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.
-
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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!