Can you have a formula populate in a child row automatically?
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.
Answers
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
- Project Manager adds new row.
- 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.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi Andrée,
Thanks for your help!
-
You're more than welcome!
Let me know if it works!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
I'd be happy to take a look so the sheet is ok to start!
Can you share the sheet/copy of the sheet?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi,
I was able to grant you admin access to the sheet. Please let me know if you are able to view it.
-
Thanks! I can! I'll take a look and get back to you as soon as I can!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi - can you explain how you accomplished this?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!