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

Richard Castle
edited 03/31/22 in Formulas and Functions

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

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!