Removing rows based on a value

I've created an onboarding training template for new hires at my company using the following (https://app.smartsheet.com/b/publish?EQBCT=2a335ca126274786adde88923187b3ca). I've removed some of the original reporting features and added our own.

I need to create a version of this that can automatically remove, and re-number days based on a flag that identifies them as a new-hire or a transfer. The goal is to remove the first week of onboarding material so that transfers can get right into the "meat and potatoes" of the content without having to manually check off orientation tasks.

We are reporting progress using a dashboard that calculates the summary sheet so we want to be able to see the difference in total days and tasks for new hires vs transfers.

Is there any automation that can remove rows based on a flag, or possibly using some if-then logic that can re-number subsequent data?

Find more posts tagged with

Sheets

Formulas

functionality

Automations

Accepted answers

Mark Cronk

Hi @Danny Marti ,

What you're asking for seems possible. However, would it be easier to add a second template for Transfers rather than use formulas to modify the original based on a [Transfer Flag}?

If you need to modify the original to do New and Transfers, here are some of the steps:

This template sets dates based on a Start Date which you enter into [DATE]2. You will need to adjust the formulas in the [DATE] column to look for the Transfer Flag and recalculate.

If you want to skip week 1 the formula in [DATE]22 would be along the lines of: =IF([Transfer Flag]=1,"", Date2) and [DATE]41 would be =IF(ISBLANK([DATE]22), Date22, Date22 +7)

You'll also need to adjust the formulas in the [DONE] column so your % complete calculations work. Easiest to just mark as complete the skipped items rather than redo all the calculations.

You could use conditional formatting to change the look of skipped rows if the [Transfer Flag] was checked.

If you want the rows removed that is more complicated. You could do it with automation that would move rows to another sheet if the [Transfer Flag] was checked. It would be easy to end up with formula messes if not done carefully.

Help at all?

Mark

All comments

Mark Cronk

Hi @Danny Marti ,

What you're asking for seems possible. However, would it be easier to add a second template for Transfers rather than use formulas to modify the original based on a [Transfer Flag}?

If you need to modify the original to do New and Transfers, here are some of the steps:

This template sets dates based on a Start Date which you enter into [DATE]2. You will need to adjust the formulas in the [DATE] column to look for the Transfer Flag and recalculate.

If you want to skip week 1 the formula in [DATE]22 would be along the lines of: =IF([Transfer Flag]=1,"", Date2) and [DATE]41 would be =IF(ISBLANK([DATE]22), Date22, Date22 +7)

You'll also need to adjust the formulas in the [DONE] column so your % complete calculations work. Easiest to just mark as complete the skipped items rather than redo all the calculations.

You could use conditional formatting to change the look of skipped rows if the [Transfer Flag] was checked.

Help at all?

Mark

Danny Marti

Hey @Mark Cronk This helps a lot, thank you. We started down the second template for the transfers path but realized that we would have our hands full updating links and content as the material changes. Right now we have a lot of rows with links to videos and training material that will change from quarter to quarter so we want to avoid updating in multiple places at all costs. If we can update it on one template, that would be ideal.

Mark Cronk

Happy to help. Thank you for using the Community.

Mark

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.