Workflow to change cell value / lock row not working when lookup between sheets shows a result
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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks @Genevieve P. I had started to go down that route anyway, but thanks for the confirmation
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!