Adding column of summary date values
I have a "Date Completed" column in my list that I would like to roll up like Start and Due Date do with sub-tasks in the hierarchy. I have been unable to figure it out with formulas. So, I am looking for the last date entered in "Date Completed" of a sub-task to auto fill that same date into the highest level of the task.
Comments
-
Hi Lindsay,
Will the row change anything after the Date Completed cell is selected?
I hope that helps!
Have a fantastic weekend!
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.
-
And.
Am I understanding you correctly that you want to show the last date added and not the latest date?
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.
-
I am leaning towards LATEST date being the requested info based off of the comparison to the Start and End Date rollups.
If that is the case, you can just use something along the lines of this...
=MAX(CHILDREN())
-
This formula worked! Is there a way to dictate, if it has children, then this formula, otherwise no formula?
I have a few lines that I want to work like the hard coded due date column. Pretty much, I am looking for a way to make my own summary value based on the hierarchy.
-
Yes, there is.
Try something like.
=IF(COUNT(CHILDREN([Task Name]@row)) > 0; MAX(CHILDREN()))
The same version but with the below changes for your and others convenience.
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, MAX(CHILDREN()))
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
Did it work?
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.
-
Andree's solution is correct for determining whether or not it has children. Just keep in mind...
While this will leave those cells blank that do not have any children, once a date is manually entered that formula will be overwritten and removed from the cell. If you were to adjust the hierarchy after manually entering a date, then you would need to replace the formula in the cell.
-
Yeah, with many users in a sheet, I can't see this being a good solution. I solution where a user could add their own summary column similar to that of duration and end date with hierarchies would be very useful.
-
Lindsay,
This sounds interesting. Can you explain further? Are you able to build a mock up of sorts to provide screenshots of what it would look like and exactly how it would work?
That way either we can help you find a more fitting solution than those already presented, or you will have a good foundation with which to Submit a Product enhancement Request.
-
In this example, I want the columns with the thick red arrows to roll up the same way the native columns in project sheet do based on hierarchy (Due Date column was renamed from End).
I am currently doing this in the Hours column with formulas; the formula gets erased though as the sheet gets manipulated which makes it not an effective way to move forward.
It would be advantageous to be able to add hard coded columns like the ones provided. This way the sheet can still be manipulated without fear of losing formulas, data, and keeping data integrity not only within the sheet, but also reports/dashboards.
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!