Clear Cell Workflow, not working with cross-sheet formula

Valeri Nguyen

Hello,

I'm trying to clear the cells from a couple columns when a date is changed, but it is not working. The date has a formula that is currently pulling in data from another sheet. Could this be the reason why?

I also tested a the clear cells workflow when a cell with a simple formula (not cross-sheet) is in it and it didn't work either.

Can a clear cell workflow work with cross-sheet formula triggered cell?

mreed

I think the trouble with this set up is that the content of the cell (formula), no matter what it's referencing, doesn't change; only the display changes. So trying to use a clear cell automation where the cell value changes to any value will never trigger.

Christina09

If you want to use cross sheet formula cell as a trigger, you need to set your automation to trigger by when a date is reached. You can have it run every day, week, or month. You might have to set multiple one for a different time if you want to run every day every hour. I have the same problem and this is my only way to work around it.

Valeri Nguyen

@Christina Lam Thank you. Can you clarify your process? Wouldn't triggering it to run daily erase every cell in the designated column? I only want cells cleared when certain dates have changed.

Christina09

@Valeri Nguyen

Under run once, select custom, this will pop cup. Select monday - sunday

click done and you should see this:

This will run every day at 11am.

As for the conditions, it depends on what you need.

It helps to screenshot your sheet so I have a better understanding of the structure.

Valeri Nguyen

@Christina Lam Thank you. This workaround unfortunately doesn't work for me because the condition doesn't include if a Date has been changed to any value.

What I'm trying to do is clear 2 cells (request install date change and install date change reason) when an Install Date is changed to ANY VALUE. The Install Date contains a formula that is cross-referencing data from another database and so it doesn't "trigger" the workflow.

Valeri Nguyen

"To prevent an infinite loop or circular reference, actions that will automatically modify the sheet cannot be triggered by inbound cell-links, cross-sheet formulas, or formulas that refer to another cell with an inbound cell link or cross sheet formula. This includes Request an approval, Move rows, Copy rows, Lock rows, Unlock rows, Record a date, and Assign people. For example, if the Status cell on the row is populated by a formula pointing to a cell with a cell link from another sheet, changes to that cell value through the cell link will not trigger a workflow that locks the row when Status changes to Complete."

Plan B....

Christina09

@Valeri Nguyen

It worked for me, I'm not sure why it didn't work for you.