Automation Question - Trigger Troubles

Lucas Rayala

I need to use the information from Sheet A to populate the fields in Sheet B; however, I am not the owner of Sheet B and many other people use it, so I can't just use formula references to make this happen. Sheet A and B are too different to automate a row copy from A to B. What I tried to do was copy Sheet B so I have a blank Sheet C with the same columns as Sheet B, then use formulas to populate Sheet C from Sheet A and have an automation move completed rows over to Sheet B. I have everything set up, but I can't get the automation to work.

I believe this is because the automations won't trigger on cells that change as a result of a formula change. Basically, I want the automation to say, when a cell in Column X equals "Y", then move the corresponding row.

That's the basics -- if you're wondering how Sheet C is populated, see below. However, the problem is it seems like I need some manual intervention in Sheet C to get automations to work. Like, I physically have to change a cell, without having a formula do that. Which defeats the purpose. I THINK I can get the automation to run every day, but that's not often enough. It would be okay having the automation run every hour, but I'd still prefer something more immediate.

How Sheet C Works (if this matters):

To populate Sheet C, I created Row ID columns in Sheets A and C; however, instead of using the auto-numbering feature in Sheet C, I pre-created about 1000 rows, with the Row ID populated from 1-1000. In Sheet A, I created a Row ID column that is auto-numbered. There's a column formula in Sheet C that looks to see if certain conditions are met in Sheet A -- when those conditions are met, the row in Sheet C which corresponds to the Row ID in Sheet A populates (i.e. Row 22 in Sheet C populates using the data from Row 22 in Sheet A when certain conditions are met in Sheet A). I pre-populated the RowID in Sheet C because that would be enough rows to last a couple of years. I don't have to worry about loops because the data in Sheet C populates based off Row ID, and once that Row ID is moved from Sheet C to Sheet A that data won't populate again. GENIUS! Right? Not so much, says Smartsheets. Help?

Jeff Reisman

I've encountered issues when the column mentioned in the trigger is also in the condition block. Since every Site Lot # entry will begin with BT, but your trigger is when Site Lot # changes to any value, just remove the condition block altogether and just go straight to the Move rows action when Site Lot # changes to any value.

Jeff Reisman

@Lucas Rayala

Can you give us some additional insight on your automation rule?

Show us the trigger, the condition block, and the action, and share the column/data types involved.

Lucas Rayala

Sheet A has an automated [Row ID] column and a flag column, [S12Testing] (this flag will eventually be formula driven, I'm doing it manually right now).

In Sheet B, the column [Site Lot #] (see below) has a formula that says:

=IFERROR(INDEX(COLLECT({SheetA_JunoLotNumber}, {SheetA_RowID}, RowID@row, {SheetA_S12TestFlag}, "Yes"), 1), "")

The rest of the row in Sheet A populates when [Site Lot #] contains "BT" (all the lot #s start with BT, so if it populates, then the whole row will populate).

I've tried several workflows. This is what I currently have:

But this doesn't run.

Lucas Rayala

@Jeff Reisman, see above. The indicated columns are all Text/Number except the auto RowID, which is an Auto-Number/System. The rest of the columns in Sheet C are a variety of cell types that match Sheet B, as the sheet was copied using Save As.

Jeff Reisman

I've encountered issues when the column mentioned in the trigger is also in the condition block. Since every Site Lot # entry will begin with BT, but your trigger is when Site Lot # changes to any value, just remove the condition block altogether and just go straight to the Move rows action when Site Lot # changes to any value.

Lucas Rayala

Ha! I know I tried that before but I think I was running into some system cache issues where it wasn't "seeing" the updates because I kept trying the same rows. I used different rows this time. Thanks!!!