Smartsheet: How to Inherit From Child and Parent Cells
There are 3 ways to inherit data from child and parent cells.
- Inheriting child cell values from parent cells
- Inheriting parent cell values from one or more children
- Inheriting subtask values from parent tasks with the card view
Method 1: Inheriting child cell values from parent cells
In this example, we have tasks as parent rows and subtasks as child rows. We want the child row's Due Date to inherit the same value as the parent row's Due Date.
To do this, we'll use the PARENT function. In the Due Date column of the child rows, type:
=PARENT()
This works but can be tiring to copy for each child row. Instead, you can select the whole column and paste the formula in all the cells at once. This way, each child row will automatically get the parent's Due Date. When you make a new parent task, you can set the parent's Due Date to replace the formula just for the parent cell.
Method 2: Inheriting parent cell values based on one or more children
In this example, we have parent tasks and child subtasks. We want the parent task's Start Date to come from the earliest Start Date of the child tasks. Similarly, the parent's End Date should come from the latest End Date of the children.
We'll use the MIN, MAX, and CHILDREN functions.
To get the parent's Start Date, use this formula:
=MIN(CHILDREN())
To get the parent's End Date, use this formula:
=MAX(CHILDREN())
These formulas pick the earliest and latest dates from the child rows.
One problem with this solution is that we must manually set these formulas for each parent row. This is a lot of work to maintain. We can improve this solution so that we don't need to manually set the formula on each parent row. We'll use two new helper columns.
We'll use our original two columns and these two new helper columns:
- Start Date (Child)
- Start Date
- End Date (Child)
- End Date
Start Date (Child) and End Date (Child) are for the child task dates, without any formula. The other 2 columns will have formulas to pick the right dates for the parent tasks.
The formula for Start Date is:
=IF(COUNT(CHILDREN([Start Date (Child)]@row)) > 0,
MIN(CHILDREN([Start Date (Child)]@row)),
[Start Date (Child)]@row)
This checks if the row is a parent or child. If it's a parent, it picks the earliest child date. If it's a child, it just uses that row's date.
The formula for End Date is:
=IF(COUNT(CHILDREN([End Date (Child)]@row)) > 0,
MAX(CHILDREN([End Date (Child)]@row)),
[End Date (Child)]@row)
This does the same thing, but it uses the latest child date instead.
To apply these formulas to an entire column, right-click a cell, and select Convert to Column Formula.
Method 3: Inheriting subtask values from parent tasks with the card view
In the card view, we often have parent tasks with subtasks. In this example, each parent task has a Due Date, and we want the subtasks to copy this same date.
SmartSheet doesn't do this automatically. So we'll use a helper column called Due Date (Parent) for the parent task dates. The child tasks will leave this column blank.
Then, we'll use a formula to fill in the Due Date for both parents and children:
=IF(NOT(ISBLANK(PARENT([Due Date (Parent)]@row))),
PARENT([Due Date (Parent)]@row),
[Due Date (Parent)]@row)
This formula checks if the row is a parent or child. If it's a child, it uses the parent's Due Date. If it's a parent, it uses its own Due Date.
Additional Resources
