Formula to copy parent data to child

Detrie Zacharias

Working with Tasks and SubTasks

I dont always have SubTasks but when I do, I'd like to bring data from the parent to the child row. Can this be done with a formula?

Paul Newcome

@Stavros_McGillicuddy I replied on your other thread.

Paul Newcome

Try this then...

=IF(COUNT(CHILDREN([Name of request]@row)) = 0, PARENT([Name of Request]@row) + " " + PARENT(Description@row))

Paul Newcome

It can be.

=PARENT()

will pull the exact data from the parent row. You can add additional text or even other formulas like this...

=PARENT() + "extra text"

or

="extra text " + PARENT()

Detrie Zacharias

Thanks Paul.

I seem to lose the formula when a new row is create via a form

Is there a way to allow a parent value to be added while keeping the formula for children?

Andrée Starå

Hi Stavros,

@Stavros_McGillicuddy

They should auto-fill if two rows before or after are structured with the same formulas.

Make sure that the below conditions are met as well.

If the formula structure is the same above/below the Formula Autofill will add the formula(s) automatically.

Conditions That Trigger Formula Autofill

You’ll see formulas populate automatically when you type in a newly inserted or blank row that is:

• Directly between two others that contain the same formula in adjacent cells.
• At the topmost of the sheet if it’s above two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• At the bottommost of the sheet if it’s below two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• Above or below a single row that is between blank rows and has formulas.

More info: 

Use or Override Automatic Formatting and Formula Autofill | Smartsheet Learning Center

https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting

Automatic formatting and formula autofill are related features that persist patterns of formatting or formulas when you add content to a sheet.

Did that work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Detrie Zacharias

Thanks Andrée

This did not work.

I dragged the formula from the 1st row to last in my "Test"column.

Using a form, I added a new record (new row at the bottom) and entered a value into my "Test" field

I opened the new row in Card View and added a SubTask

The value I entered in my form>Test field did not carry down to the SubTask.

I think by adding a value into the field that has the formula in it, I am breaking the continuation of the formula

Andrée Starå

@Stavros_McGillicuddy

Happy to help!

Yes, if there is a value in the formula field that will break the chain.

We could structure the sheet with so-called helper columns to make it work.

Is that an option?

Detrie Zacharias

I am trying to bring [Name of Request] and [Description] from a PARENT into a CHILD row and leave the value blank if the row is a Parent

This throws an #UNPARSABLE error

=IF(COUNT(CHILDREN([Name of Request]@row)) = 0, PARENT(([Name of Request@row)+(Description@row)))

Paul Newcome

"I think by adding a value into the field that has the formula in it, I am breaking the continuation of the formula"

You are correct. As soon as you enter a value into the cell, the formula is removed. Once the formula is removed, it is no longer there to meet the requirements of autofill.

You could create another column that uses a formula to account for both and use that one for display. Something along the lines of...

=IF(COUNT(CHILDREN([Text Column]@row)) = 0, PARENT([Text Column]@row), [Text Column]@row)

This will first count the children. If there are none (meaning it is a child row), it will pull the parent. Otherwise it is a parent row in which case it will pull the row data.

Detrie Zacharias

Ah.. That is the direction I started to go.. I created a new discussion HERE

...

I am trying to bring [Name of Request] and [Description] from a PARENT into a CHILD row and leave the value blank if the row is a Parent

This throws an #UNPARSABLE error

=IF(COUNT(CHILDREN([Name of Request]@row)) = 0, PARENT(([Name of Request@row)+(Description@row)))

Paul Newcome

Do you want the parent data of both columns in one cell on the child row?

Paul Newcome

@Stavros_McGillicuddy I replied on your other thread.

Detrie Zacharias

Yes... Both columns into one cell of the child

Paul Newcome

Try this then...

=IF(COUNT(CHILDREN([Name of request]@row)) = 0, PARENT([Name of Request]@row) + " " + PARENT(Description@row))

Detrie Zacharias

Thanks Guys for your help

Detrie Zacharias

There it is .... I was missing the "PARENT" on the Description field.

Thanks Guys for your help

Paul Newcome

Happy to help! 👍️