Set a row to 'not applicable'

Hi. My project contains 3 sheets to represent network areas and includes the site locations within the networks. The project tasks are the same and include deployment stage gates and user adoption stage gates. We would like to use the same set of stage gates across all of the network areas for consistency and reporting, but some do not apply to a particular site location. We'd like to keep these but mark them according so we know we didn't miss a step. Is there a way to mark a row as 'not applicable' so that it doesn't get considered in the parent row % rollup?

Thanks for your insights!

Find more posts tagged with

Sheets

Formulas

Reports

Comments

Andrée Starå

Hi Ilene,

Yes, but it would have to be manually calculated.

Would that work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

I use this A LOT. I have a checkbox column called [Needed for Client?] with all rows defaulted as checked. We un-check it if that particular row isn't needed. I have some conditional formatting in place to gray it out and strike through it, and in the Status column (Not Started/In Progress/Complete) I have it say "Not Needed".

Here's the part that actually answers your question...

In all of my rollup formulas, I include the criteria of if that box is checked.

=COUNTIFS(Status:Status, "Complete") / COUNT(Task:Task)

changes to

=COUNTIFS(Status:Status, "Complete") / COUNTIFS([Needed for Client?]:[Needed for Client?], 1)

and that gives me my percent complete while also skipping over the rows that are not needed.

ilene_healy68056

Thank you!! This could work....I will give it a try.

Paul Newcome

Happy to help!

Let me know how things go, and if there are any issues, I'd be more than happy to continue working on this with you.

ilene_healy68056

Thank you so much Paul. I appreciate it. I will work on it today and will take you up on that :-)

ilene_healy68056

Just a follow up...In my project sheet, I have Start Date, End Date and % Complete. I have been letting Smartsheet auto calculate these fields at the parent rows based on what is entered at the child. So, the % is auto populated on the parent based on the % of the children and the durations. With the need to now exclude the 'not applicable' children (eg Adoption), do I have to code over what Smartsheet already does for me and create my own rollup % complete on the parent? Let me know if that isn't very clear.

Thanks for the feedback!

2019-06-05 12_33_19-SP Automation - ExM - Smartsheet.com_.png

Andrée Starå

That is exactly right!

Also, you have to turn it off in the Project Settings.

Best,

Andrée

ilene_healy68056

Thanks for clarifying....because it saddens me to turn off that logic that works so perfectly (and the coding needed is probably beyond my skillset), is this a viable workaround? If I add the rule to gray out and strike-though rows that are 'not applicable', and then if I ensure that the Start Date, End Date, Duration and % Complete are BLANK for the children rows, will the parent roll up ignore those rows? It seems to be the behavior I see for where people have failed to fill all of those key fields and the parent row is showing a % less than expected. See my screen shot.

Thanks!

2019-06-06 10_11_45-How Smartsheet calculates % Complete (002).pptx - Read-Only - PowerPoint.png

Paul Newcome

The coding that replaces having dependencies enabled can either be relatively straightforward or massively painful. It all depends on your specific needs, use, and details.

If you are interested in pursuing this option, let me know. I'll have some questions for you, but then we can move on to some testing.

I actually almost never use dependencies and have built some rather complex (and some easier ones too) sheets to replace the dependencies.

ilene_healy68056

Hi Paul

Thank you so much for your help. I would definitely be interested to learn what I can do to solve for this. I don't believe our needs are very complicated, so perhaps there is a better straight-forward solution that allows me to keep the 'N/A' feature and code for the summarized % complete and dates. Let me know what works for you.

Thanks

Ilene

Paul Newcome

First and foremost... Are you actually using the % Complete column in Gantt view?

ilene_healy68056

We are only in the process of setting up the sheets....so we haven't crossed that bridge. For now we are creating summary sheets and reports from the % complete. Hope that helps

Andrée Starå

Happy to help!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée

Paul Newcome

Ok. Because my method does not support Gantt view. You have to have dependencies enabled to use Gantt view effectively, and my process has them disabled.

Do you have any holidays or non-working days of the week that will need to be accounted for?

What type of durations are we talking for the projected start and finish dates themselves? Days, weeks, months, years, hours, etc? I have mine set up for a very basic Projected Start on Monday and Projected Finish on Friday with week assignments for each row. Most of my deliverables are more similar to milestones than tasks, so we have it set up where as long as xyz documentation is received from the client during week 3 of the project, we consider it as still on track regardless of which day of the week it was received.

ilene_healy68056

Hi Paul

Sorry for the delay....I am a new user and the amount of formulas needed to make it all run seems a bit overwhelming at the moment. For now, I will need to leave the default dependency logic as is. I will see how that works for us or if the "Not Applicable" rows cause a problem. If I need to code over that and turn off the dependencies, I will reach out. But for now, it's one less thing for me to break. LOL. Thank you for your time and your patience.

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.