How do I create and If Formula pulling from another sheet to designate indicators?

Options
delaurellc
delaurellc ✭✭✭✭
edited 06/09/23 in Formulas and Functions

Good Morning All,

I am working on a Overall Health for a Project that looks like this:

image.png

I need to update the lights by using an if statement looking at three different columns of another sheet that are not next to one another on the sheet, example of that sheet below

image.png

In the first sheet for each Column I need to look at the three rows Criteria Most Impacted, Due Date and Done to determine the color of the light, Green, Yellow, Red.

Lets use Infrastructure as an example

on the first sheet referencing the second sheet the code would need to do the following:

If the Criteria Most Impacted is equal to Infrastructure and the Due Date is greater than 5 Days and the Done Column is not checked, the indicator should be Green.

If the Criteria Most Impacted is equal to Infrastructure and the Due Date is between 1 to 4 Days out and the Done Column is not checked, the indicator should be Yellow.

If the Criteria Most Impacted is equal to Infrastructure and the Due Date Equal to or Less than day or is overdue and the Done Column is not checked, the indicator should be Red.

I am more of a DB analytics person so the calculation is done in SQL or ABAP code, I am a bit lost in the Smartsheet code.

HELP!

Caroline

Best Answers

«1

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!