Conditions / Rule formula set-up

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

Find more posts tagged with

Construction

Formulas

Support

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.