Workflow to change cell value / lock row not working when lookup between sheets shows a result

Richard Castle

Hi. Wondering if anyone is able to assist here. We have 2 sheets (DA and POC). Both have individual unique reference numbers. When I create a new row in DA (unique ID = DA0001) there is a field in DA0001 where you can add a unique ID from POC (example POC0005). So the row would look like:

DA Ref DA Name POC Ref

D0001 TEST POC0005

I have indexmatch and lookups so that a cell in POC0005 shows that it is linked to DA0001. So the row would look like:

POC Ref POC Name DA Ref

POC0005 Task 1 =index match [POC Ref]@row with Sheet DA and column POC Ref and return DA Ref) which here = D0001

What I am trying to do is to lock POC0005 when the cell DA Ref is populated.

I tried to set up a workflow to say "When DA Ref is not blank" then Lock Row

But I have realised that a workflow won't kick off directly from the lookup refreshing so instead have a checkbox with a formula in that is checked when DA Ref is not blank:

POC Ref POC Name DA Ref In DA?

POC0005 Task 1 =IF(NOT(ISBLANK([DA Ref]@row)), 1, 0) which here = 0

POC Ref POC Name DA Ref In DA?

POC0005 Task 1 D0001 =IF(NOT(ISBLANK([DA Ref]@row)), 1, 0) which here = 1

The workflow would then go "When In DA? changes to Checked", then lock row

But this workflow isn't working either

HOWEVER

If I change the workflow to go "When In DA? changes to Checked", then just send an alert

This IS working

Just wondering if anyone has any other suggestion or is this a bug that I should report with Smartsheet?

@Andrée Starå this might be an interesting one that you might be able to share some light on- I have used your answers to a number of things before so would be humbled to get your opinion!

Thanks

Genevieve P.

Hi @Richard Castle

This is currently expected behaviour: actions that change a sheet (such as Locking a row, Clearing a cell, Moving a row, etc) cannot be triggered by cross-sheet references or formulas that reference a cross-sheet reference or cell link.

Since your checkbox IF is referencing a cross-sheet formula, it can't be used as the Trigger to Lock the row (although it can be used as a Trigger for an Alert, which is why you're seeing a difference in functionality).

See the Note in this Help Article: Trigger Blocks: Define When Your Workflow is Executed

I would suggest setting up a daily Time Based workflow instead, locking the rows each day with a Condition Block that identifies the formula cell is not blank.

Cheers,

Genevieve

Genevieve P.

Hi @Richard Castle

This is currently expected behaviour: actions that change a sheet (such as Locking a row, Clearing a cell, Moving a row, etc) cannot be triggered by cross-sheet references or formulas that reference a cross-sheet reference or cell link.

Since your checkbox IF is referencing a cross-sheet formula, it can't be used as the Trigger to Lock the row (although it can be used as a Trigger for an Alert, which is why you're seeing a difference in functionality).

See the Note in this Help Article: Trigger Blocks: Define When Your Workflow is Executed

I would suggest setting up a daily Time Based workflow instead, locking the rows each day with a Condition Block that identifies the formula cell is not blank.

Cheers,

Genevieve

Richard Castle

Thanks @Genevieve P. I had started to go down that route anyway, but thanks for the confirmation