Automation with OR

KarineMul22_TPg

Hello,

I am trying to do an automation to Change the Status depending on Criteria clicked on the same row. I can do it with the trigger being the latest box ticked, but sometimes it doesn't happen in order, so for example:

Status to become Ready for review by PE if Column 2, 3, 4 and 5 ticked, doesn't matter in what order - but my automation would say

Trigger = Box 5 ticked

Condition = Box 2, 3 and 4 ticked

I thought to put

Trigger = any change

Condition = Box 2, 3, 4 and 5 ticked

But nothing at all was working anymore

Thank you for sharing :)

KarineMul22_TPg

Hi Debbie,

No the destination sheet looks like an organigramme so nothing like a tracker, so no proper rows or columns - my only hope was to bring the colours from the source sheet :(

Paul Newcome

Are you able to share some screenshots for context?

KarineMul22_TPg

Paul Newcome

Try setting up multiple OR triggers with multiple AND conditions.

KarineMul22_TPg

Thank you but that doesn't work

Debbie Sawyer

@KarineMul22_TPg

I am surprised that Paul's suggestion didn't work.

Have you considered using a nested IF function to set your status? Instead of the workflow?

In the status cell put:

=IF(AND([Box 1]@row=1,[Box 2]@row=1,[Box 3]@row =1), "Ready for Review by PE","")

The false part (last "") could either have another Text Value showing the Status if the boxes are not yet checked or it can also contain another IF function checking your next part of the process.

Happy to help further on a Zoom call if required.

Kind regards

​Debbie Sawyer - Chief Smartsheet Solutions Officer (CSSO)

 

KarineMul22_TPg

Thank you Debbie, however I have 15 different status, it's going to be a very long and complicated formula... but if you think it's the only way, I will give it a go

Regards,

Karine

Debbie Sawyer

I write lots of solutions for many clients, and a nested IF is my preference. I often have a very long and complicated formula, but if it works then that is great!

Remember if nesting when you read the formula back as soon as the IF returns true, then the rest of the function is ignored. So you have to work backwards (if you see what I mean). Start with the condition that sets the final status, then the penultimate status working back to the first one. This works for me. If very complicated, you could maybe split it across a couple of helper columns.

@Paul Newcome I know you are a very experienced Smartsheet Consultant, do you set Status using nested IF's or do you the Set Value workflows?

Good luck! :D

Break it all down and work through bit by bit :D

Paul Newcome

@KarineMul22_TPg Can you provide a screenshot of the setup that didn't work?

@Debbie Sawyer Generally speaking I try to set up formulas. There are 2 instances where I will use automations. First is when we need a mix of manual and automatic. Second is in cases like this where the user already seems a little more familiar/comfortable with automations over formulas.

KarineMul22_TPg

@Debbie Sawyer @Paul Newcome

Hello, so I started the Nested IF(AND formula, but because our status are quite long and descriptive, the way we want them, I can not finish my formula, it is way too long, no more characters accepted... so I do need a solution with Automation please

Debbie Sawyer

Happy to zoom to discuss ideas with you @KarineMul22_TPg

Paul Newcome

@KarineMul22_TPg Is it possible to use a prefix for your statuses such as 01, 02, 03, etc.?

When you said the automation didn't work, what exactly did you mean? Did you get some sort of error message, or did it not change the cell? Are you able to provide screenshots of exactly how the automation was set up? I use that method rather frequently.

KarineMul22_TPg

Hi Paul,

I suppose I could use prefixes but would need to see the full descriptive in the Tracker, not have to go to a Legend part to effectively know what it means...

The automation didn't work because there was no change in cell, I will try again soon and will share with you.

In the meantime, I am working on something else and I have a question, do you mind? It's pretty basic, sorry, but I am trying to Copy a cell from a sheet to another and want to keep its colour as well as it's text, it is coloured by Conditional formatting in the first one.

So far, I have tried simply "Link from Cell in Other Sheet..." and also a Vlookup, but each time, only the text come through. Would you know how to?

Many thanks

Karine

Debbie Sawyer

I've had many a problem trying to get the colours to stay true in copying cells. If you copy the whole row, the colours are supposed to stay true to the original, only works on whole row copying though.

Are you not able to set up the conditional formatting again in the destination sheet? I've found the colour copying on whole rows to be a little unreliable in the past.

KarineMul22_TPg

Hi Debbie,

No the destination sheet looks like an organigramme so nothing like a tracker, so no proper rows or columns - my only hope was to bring the colours from the source sheet :(

Paul Newcome

@KarineMul22_TPg Hmm... How many different checkboxes do you have, what are each of the statuses, and what is the logic behind each that you are trying to accomplish?

I am thinking we may be able to set up a reference table and use a formula to pull in the appropriate status that way as well.