Any cell below the stock Level, RYG become to red.

Hi,

As per the attached and formula, How can i add the formula if Week 19,20,21~52 any column below the stock Level, RYG to red.

I'm only know the formula for single cell.

=IF(ISBLANK([Stock Level]3), "Yellow", IF([Stock Level]3 <= [Week 19]3, "Green", "Red"))

Thanks in advance.

Screen Shot 2018-04-11 at 12.33.18 PM.png

Find more posts tagged with

Comments

Shaine Greenwood

Hi Richard,

Use the AND function in your formula:

=IF(ISBLANK([Stock Level]3), "Yellow", IF(AND([Stock Level]3 <= [Week 19]3, [Stock Level]3 <= [Week 20]3, [Stock Level]3 <= [Week 21]3),"Green", "Red"))

The above formula returns green as long as all three are equal to or above 5000. If any of them fall below 5000, it should return red. More on AND in the help center: https://help.smartsheet.com/function/and

Here's our complete function list: https://help.smartsheet.com/functions

richard_uy

Hi Shaine,

It won't work, is there any formula missing?

Shaine Greenwood

Hi Richard,

Can you give me more information? What's not working, specifically? Are any errors not being returned? Is it just not returning what you expect it to?

richard_uy

Hi Shaine,

There is no responds when i input the qty into week 19 & 20. the Formura will work only when i type the content into Week 21.

=IF(ISBLANK([Stock Level]3), "Yellow", IF(AND([Stock Level]3 <= [Week 19]3, [Stock Level]3 <= [Week 20]3, [Stock Level]3 <= [Week 21]3), "Green", "Red"))

Shaine Greenwood

Hi Richard,

That's due to the AND function, which requires that all conditions be met before setting the status to Green, otherwise it will return Red.

If you're looking for a function that returns Green when at least one condition is met, use OR in place of AND. I think that you'd also want to have the formula return Red if they fall below the stock level. Try this:

=IF(ISBLANK([Stock Level]3), "Yellow", IF(OR([Stock Level]3 > [Week 19]3, [Stock Level]3 > [Week 20]3, [Stock Level]3 > [Week 21]3), "Red", "Green"))

Sign in to join the conversation!

Quick Links

Trending discussions

Document filtered by form responses
I've created a Smartsheets form. When someone submits the form, I want them to automatically be emailed a document that has resources and links, but the document is filtered to only show the content that corresponds with what they selected in the form. Is something like this possible? What types of documents or automations…
Form stopped working after upgrade
I have a sheet that I created in a previous version, and it had a form. I was recently upgraded to a newer version and now my form is not working. I created a new form for the sheet, and it is giving me an error also. Does anyone know what I can do to fix this? The error says Form Unavailable. Sorry, the form you're trying…
Getting Error About Emulated Environments
Hi, I have read the post from 8/21/25. I am still getting the same error. I am setting up a new device and just download the latest version as of 9/5/25. I am using a Eunivon Smart White Board for touchscreen capability. It is running Android 14. Any help would be appreciated.