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

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?