Automation with OR

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 :(

All comments

Are you able to share some screenshots for context?

Try setting up multiple OR triggers with multiple AND conditions.

Thank you but that doesn't work

@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)

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

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

@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.

@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

Happy to zoom to discuss ideas with you @KarineMul22_TPg

@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.

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

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.

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 :(