Can you have a formula populate in a child row automatically?

Melissa.Holland80446

In our project tracking sheet we have a column for Project/Task Name. The project name is listed in the parent row and then any tasks are listed below as child rows. The projects are added to the sheet by the Director who also assigns a project manager. The project manager then adds tasks and indents them as child rows under the project. We find card view very valuable and want to have lanes by project. In order to do this I have added a column "Project Name - Used for Card View". I would like to hide this column and have it automatically populate the project name.

Is there a way to have a formula populate in a cell once the row is indented as a child?

I've tried only adding tasks via form - but the form will not allow me to use a formula as prepopulated data when it is a dropdown column.

I've updated the formula for projects/tasks at the top and bottom of the sheet. It seems with hierarchy it does not recognize the pattern.

Any help would be greatly appreciated! I have attached a screenshot example.

Andrée Starå

Hi Melissa,

Not sure I follow!

Yes, there is a way to have a formula check if it’s a parent or child. Something like.

=IF(COUNT(CHILDREN(ColumnName@row)) > 0; "TRUE")

For formula autofill to work, there have to be two or more cells with the same before or after. The formula cells should not be in the form. 

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Melissa.Holland80446

Hi Andree,

I am replying to your comment on the community with more detail. Here is the sheet example. 

I am using Datamesh to pull in Projects from another sheet. So Datamesh automatically creates a new row. That row is starred as a project where conditional formatting is applied making it gray. The project column is hidden from users. 

The Project Manager "Assigned To" gets an alert when a project has been added and is assigned to them. They then go into the sheet and add new rows for any tasks that they need to complete for the project. 

1. Project Manager adds new row. 
2. Moves row under project header and indents as child row. 

I have a column "Project Name - Used for Card View" I want the project name to populate in that column in all rows. I can manually add the formula to = the parent row. 

I have 100+ project managers and do not want them to have to add formulas. Looking for a way for the Project Name to populate in the entire column. 

Let me know if this helps. 

https://app.smartsheet.com/b/publish?EQBCT=ec5601e15b7b4c7b92e0ecc577b6593e

Andrée Starå

Thanks!

Try adding the formula but add a ' before because Smartsheet will ignore it and activate the formula. (untested with Datamesh, but I think it will work)

Let me know how it goes!

Melissa.Holland80446

Hi Andrée,

Thanks for your help!

Andrée Starå

You're more than welcome!

Let me know if it works!

Melissa.Holland80446

Unfortunately that did not work. Here is another screenshot that may help.

I want to project name to display in the column on the right for each of that tasks that are a child row below that project. I am using the formula =PARENT().

I have to manually put in the formula every time a new task is added.

A new task can be added via form or directly in the sheet.

Do you think you could help? Let me know if this example is helpful.

Andrée Starå

I'd be happy to take a look so the sheet is ok to start!

Can you share the sheet/copy of the sheet?

Melissa.Holland80446

https://app.smartsheet.com/b/publish?EQBCT=648997d32e4c440ca47b0cdbc13c1449

Andrée Starå

Can you share it to me directly because I can't see/check everything in a Published sheet?

(Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Melissa.Holland80446

Hi,

I was able to grant you admin access to the sheet. Please let me know if you are able to view it.

Andrée Starå

Hi @Melissa.Holland80446,

Thanks! I can! I'll take a look and get back to you as soon as I can!

Andrée Starå

@Melissa.Holland80446

Glad we got it working!

For everyone else, we concluded to have a section or row rows to copy that would include the formula that's needed.

Best,

Andrée Starå

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

Rachel Komenski

Hi - can you explain how you accomplished this?