Conditional Sheet Population

Is it possible to use conditional formatting or formulas to suppress unnecessary tasks?

And, if so, could Smartsheet adjust dependencies for removed tasks?

For instance, we have a sheet to track product implementations; however, some products are not elected by a client and therefore we do not have to track their related tasks. And, each task has a dependency, so if the conditional formatting removes a task, we want the dependencies of the remaining tasks to adjust automatically.

Find more posts tagged with

Sheets

Formulas

Automations

Conditional Formatting

Project Management

Accepted answers

Tony Fronza

Thanks again, @Genevieve P.! I was thinking the multi-template route was the only way to go, but I wanted to make sure I wasn't missing anything. I greatly appreciate your quick and informative replies.

All comments

Genevieve P.

Hi @Tony Fronza

Conditional Formatting will only adjust the formatting of the row; this means you could automate unnecessary tasks to be turned pale grey or have the data "crossed out" with a line through the middle, but this view won't affect the project timeline or dependencies. It would just be a visual indicator.

You could adjust any unnecessary tasks to have a Duration of 0 so that the following tasks start earlier. Project columns (Date / Predecessor / Duration) cannot contain formulas or be populated via a workflow, so adjusting the Duration to be 0 would need to be done manually.

For example, you could set up Conditional Formatting to cross out the row if the checkbox is checked for the row not being needed. Then you could also set up a rule that says if the Checkbox is Checked but the duration ISN'T 0 yet, highlight the row so it's easy to find and adjust:

Screen Shot 2022-03-24 at 1.13.51 PM.png

When you manually change the duration to 0 for the unnecessary task, it will go back to being a normal colour:

Screen Shot 2022-03-24 at 1.14.20 PM.png

As an alternative, you could remove tasks from the Project Plan by removing them from the sheet. You could set this up to be automatic (based on a trigger, such as a manual checkbox if you note that this row isn't needed) with a Move Row workflow.

User: "Screen Shot 2022-03-24 at 1.06.20 PM.png"

However removing a row like this would break any rows that were dependent on that task (you'll see a "REF" error):

User: "Screen Shot 2022-03-24 at 1.08.09 PM.png"

This error could be your indicator that you'll need to manually adjust which Predecessor the task looks at. I hope this helps!

Cheers,

Genevieve

Tony Fronza

@Genevieve P. thank you so much for your detailed reply! I figured retaining dependencies would be tricky, but I'll try the conditional formatting for setting duration to 0 for unnecessary tasks. Could I create a form that populates a sheet with only rows based on the options a user selects?

Genevieve P.

Hi @Tony Fronza

Forms populate a sheet one row at a time, so I don't believe this would be a good solution in your instance. What I would do here is have a few different templates set up and ready for the different project types needed. Then your users can create a Project sheet based on what tasks will be needed.

See: Templates: Create Your Own or Use One from the Solution Center

Cheers,

Genevieve

Tony Fronza

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.