Conditions / Rule formula set-up

Mary Miller

I am working on a sheet, and I am very new to this application, where I am tracking P.O.'s, date ordered, date received, etc... and I have 2 columns (at the far right of my sheet or end) that I would like to 

If the P.O. Total is < or = to the Invoiced Amount the cell would turn green and  if it was > than to turn the cell red.

I have tried numerous ways but I guess my brain cant rap around the logic the designers used when creating these formulas.  I need a bit of help please.

 

Thank you in advance,

Mary Miller

Mike Wilday

Hello Mary, 

I would add a helper column to your row. Add an RYG Symbol column and put this formula in it. =IF([P.O. Total] > [Invoiced Amount], "Red", IF([P.O. Total] <= [Invoiced Amount], "Green")

This will give you a green stoplight. You will use this as helper column for turning the cell green. 

Click on the Conditional formatting tab in the toolbar. 

Click on ADD NEW RULE. 

And then you get to set your condition to if the RYG symbol = green. 

And then you can change "This Format" To the green color. 

Then change "entire row" to the column name you want green. 

Mary Miller

Good morning Mike!

Thanks so much for the timely response, however I have sat here now for about an hour going step by step from your formatting suggestions and still can not get this to work the way it should.  These formulas are kicking my butt. Smartsheet is a great tool, but does not help if you can't use it as a beginner right out of the gate like excel, (as I am only one week into working with Smartsheet). I am getting frustrated, but I will eventually get the hang of it, I hope.

I set up a new column and labeled it RYG status and as you instructed then created the formula:

. =IF([P.O. Total] > [Invoiced Amount], "Red", IF([P.O. Total] <= [Invoiced Amount], "Green"), as you also recommended I do.

Now I have tried to go into the conditional formatting and create a new rule, but I am not sure under what column/cell this needs to be done in.  The RYG status column or my Invoiced Amount or P.O. Total column??? As I have tried doing this in each column one try after the other and am still having trouble. Any help or suggestions you have would be greatly appreciated.

Thank you Mary Miller

 

Andrée Starå

Hi Mary,

The formula should be in the RYG Status column cells. 

Do you get an error message in the cell(s)?

The conditional formatting should be set up to look at the same column with criteria for RYG. 

Does that work?

”I’m on my mobile so I can't be more specific right now”

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Mike Wilday

Hi Mary, 

You need to choose the column type to be SYMBOL. See my screenshot.

Set the RYG column to be a symbol column by right-clicking on the column header and choosing properties. Then you can change the column type to symbol and choose the options in my screenshot. 

Then your conditional formatting should be based on when your RYG column is Red, then changed the To this row.... to make it apply to the column you want to turn red... 

Mike Wilday

I also noticed the following mistake with my formula. I forgot the @row. Add this formula to the RYG column. And it will check the amounts on that row and set a red or green stoplight for that column.

=IF([P.O. Total]@row > [Invoiced Amount]@row, "Red", IF([P.O. Total]@row <= [Invoiced Amount]@row, "Green")