We need a function where users can de-scope rows, thus deactivating that row and removing its influence on its parent rows. Is that an option via Smartsheet?
Hi Brent,
Yes, it is! One way could be to have a check-box column that if checked it won't be included in the parent calculation. A formula would decide if it should be included or not.
Would that work?
Hope that helps!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
That would be perfect! I was already thinking a checkbox function. What would that formula look like?
One thing to keep in mind with this...
If dependencies are enabled and the parent rows are automatically a rollup of the child rows, checking the box will NOT remove it from the calculations.
This will only work if the dependency settings are turned off, and you are using your own formulas in the parent rows.
Thanks Paul. That's a great point. Fortunately we don't have too many dependencies but I will make a note for our presentation when this feature goes live.
Happy to help.
Hi Andrée, I've set up the checkbox functionality and conditional formatting to mark the row as "Descoped" once a user enters notes in the appropriate column.
However, I have no idea where to start on the formula to remove the row from its respective parent once the status is "Descoped". Would you please help men on that? Thank you!
Happy to help!
Try something like this.
In this example, you'd add the formula to the parent where the values you'd want to sum is.
=SUMIF(CHILDREN(Status@row); "Descoped"; CHILDREN())
The same version but with the below changes for your and others convenience.
=SUMIF(CHILDREN(Status@row), "Descoped", CHILDREN())
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."
Can you describe your process in more detail and maybe share 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)
Here are two screenshots of our current sheet. Screenshot #1 is our sheet before descoping.
When users want to mark a row as "Descoped", they must enter text in "Descoped Notes". When they do, a hidden checkbox cell will become checked. The row will then turn gray and the health ball will disappear (see screenshot #2).
Ideally, when the row is descoped, I would like the row's date and % complete information to be pulled out of its parent row roll-up so that it is not calculated.
Please note that I currently have dependencies enabled on this sheet.
As Paul mentioned it will only work if you don't need dependencies.
Do you need to use it?
Dependencies CAN be worked around, but depending on your needs it can get rather complicated rather quickly to replace the automation with your own. Working in multiple blackout periods for holidays, weekends if your durations are anything different than a week, predecessors, lag/lead time, etc... It can all be built in, but each aspect adds in layers upon layers of complexity.
For example... My project plans are about 650 rows each. The duration of each task has been set to weeks (1 week, 2 weeks, 3 weeks, etc.) which fortunately works rather well for what we do here. I have a 2 week blackout period for Christmas/New Year holiday. 1 week for Thanksgiving, and a blackout period for specific deliverables from the 15th of December to the 15th of January along with taking into account putting the project on hold.
We have 12 columns for display (things such as task name, owner, assigned to, needed or not, risk, duration, etc.).
But then it took 93 hidden "helper" columns to replace the automation of the dependencies in order to take into account all of the blackouts and holidays and whatnot. And that's with the easy duration of weeks for everything.
So... It is possible, and it could be relatively straightforward. It just depends on your needs which drives the complexity.
Yes, of course. I meant in a more general sense and the built-in feature.
93 helpers, that's nothing. I think I used something like 160 at one point
160?!? My sheets are getting obnoxiously slow already! You must be running on a much better computer than I am. Hahaha
You're right. I apologize. I'm still new to Smartsheet and trying to understand everything.
Since I'm so new, it would be easier to keep dependencies on. I have the automation established to mark rows as "Descoped" based on a hidden checkbox column. Since we don't have a ton of descoping to do, I will just manually move tasks to a separate sheet when descoped. We don't want to delete them permanently but also don't want them rolled up into the parent rows.
I appreciate both of you looking into this for me!
No need to apologize. We’re here to help!
Because it’s ok to move the row you could maybe use the third-party service, Zapier to automate it.
If you are moving rows, but don't want to delete them permanently...
You could create a new parent row that is separate from the rest of the sheet (but still on the same sheet) and move descoped rows to be children of that parent row. That way you maintain the visibility of "we have it but don't need it for this project", and it will keep it separate from the other actual deliverables.
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
