can i auto-populate/create rows on another sheet if a cell on another sheet changes?

leoc
leoc
edited 01/15/25 in Smartsheet Basics

In this case, I'll populate a scheduling sheet as seen on top, this will then ideally show up on the 18th of Dec (if it's scheduled on the 18th) as separate rows if possible. Not sure if smartsheet is able to do something like this so would like to get possible help or input?

So far i've tried using smth like

=IF(OR(CONTAINS("Truck In", {xxx1}), CONTAINS("Replacement", {xxx1}), CONTAINS("Truck In/Out", {xxx1})), INDEX({Date}, MATCH("Truck In", {xxx1}, 0)), ""); which does update ONE cell but it doesn't work when I apply it to the 2nd column because my truck schedules are all in columns but in one row im assuming it doesn't create more rows

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion

    Hi @leoc

    The solution I came up with uses workflow automation.

    1. When one of the hose columns in the Scheduling request sheet changes to one of the three work type values, "Truck In," "Replacement," or "Truck In/Out," the cell values of a helper column in the [Scheduled Hose] dropdown list are changed to one of the hoses.
    2. When the [Scheduled Hose] value changes, copy the row to another sheet, Scheduling Sheet, in my example.
    3. With the new row copied, update the description primary column with a formula that combines the [Scheduled Hose] value and one of the three work type values like "Truck In."

    Scheduling request sheet

    https://app.smartsheet.com/b/publish?EQBCT=625b3f91e1e245cd96c6f3050c4fc653

    Workflow Automation

    Copy rows automation

    Change cell value automation (Example 1)

    Scheduling Sheet

    The Scheduling Sheet has this formula in the Description column.

    [Description] =[Hose Scheduled]@row + ": " + IF(CONTAINS("Plot 1 RO", [Hose Scheduled]@row), [Plot 1 RO Con Tank NHW (3inch hose)]@row, IF(CONTAINS("Plot 1 Aeration", [Hose Scheduled]@row), [Plot 1 Aeration Tank (1.5inch Hose)]@row, IF(CONTAINS("Plot 2 CHW", [Hose Scheduled]@row), [Plot 2 CHW (3inch hose)]@row)))

    The IF part of the formula chooses one of the three columns to show which work type to add to the description.

    https://app.smartsheet.com/b/publish?EQBCT=625b3f91e1e245cd96c6f3050c4fc653