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

delaurellc

Good Morning All,

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

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

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

Paul Newcome

If it isn't weekly, then you would use a COUNTIFS in nested IFs.

=IF(COUNTIFS({Criteria}, @cell = "Infrastructure", {Due Date}, @cell<= TODAY())> 0, "Red", IF(.....

You would continue that out and adjust the number of days in the TODAY() function to determine your RYG.

Paul Newcome

{Done}, @cell = 1

is looking for rows where the box IS checked. We want to look for rows where the box IS NOT checked.

{Done}, @cell <> 1

Paul Newcome

How would you know which week it is to be applied to?

I also want to point out that it looks like you tried to block out a couple of contact columns, but it ended up being more of a purple highlighter and the underlying data can still be seen. I am not sure if that is an issue or not but wanted to let you know just in case.

delaurellc

Paul,

Not worried about the contact stuff hopefully no one will use it for anything and it will not be used in the calculation.

The weeks are something that I think the person working this sheet was not thinking about. it is kind of a rolling thing. first week takes into consideration anything on the sheet that does not have the done check box. second week will take into consideration anything on the sheet that does not have the done in the check box, no matter when it was added, and so on. So each week we would look at all the data on the sheet sheet at end of the week that did not have a done box checked.

I know the code needs to be there so if we were to add it using next week as week 1:

Week 1

If the created on date was equal to or less than 06/17/2023 and

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.

Week 2

If the created on date was equal to or less than 06/24/2023 and

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.

This would be replicated for each week of the project some which go on for over a year.

Complicated I know but all we do is projects, some short term and some long term. Can this be done?

Thanks,

Caroline

Paul Newcome

The first step is to get some consistent dates. If you add this to a date type helper column in the source sheet, we will be able to capture the Monday of whatever week the data was submitted on:

=DATEONLY([Date Submitted]@row) + (2 - WEEKDAY(DATEONLY([Date Submitted]@row))) - IF(WEEKDAY(DATEONLY([Date Submitted]@row)) < 2, 7, 0)

Now we can insert a date type column on the RYG sheet with this formula:

=MIN({Source Sheet Helper Date}) + ((VALUE(RIGHT(Week@row, LEN(Week@row) - (FIND(" ", Week@row) + 1))) - 1) * 7)

Applying this as a column formula should give us the earliest Monday from the source sheet and then just keep adding 7 days as you go down the column.

From here we can use INDEX/MATCH or INDEX/COLLECT formulas to pull the appropriate data over for each week from the source sheet and nest them in IF statements to generate your RYG. The only part I am needing some clarification on is the Due Date. Do you mean if the Due Date is (for example) five or more days in the future as compared to TODAY()?

delaurellc

I am certain I am not explaining this correctly as I am not moving data at all I am just looking at the data to designate a single light for each area. When I do SQL or ABAP code it is a simple table READ or LOOP to find all the items and then it will check each one and once it finds something that meets the criteria it stops and sets the indicator.

I also think the person who built the indicator sheet thought we could hold historical numbers but we cannot, so let me try this one more time and I apologize for all the confusion, this should be easy for me and I am feeling kinda dumb right now :o(

remove the week from the first sheet it should just look like this

these lights will be populated by looking at the second sheet for every row that has one of the above designated within it. It will go through each row and if it finds any row that has the If statements for Yellow or Red it will be Yellow or Red.

So lets say our lookup sheet has five rows of Infrastructure issues open. 4 of them are fine do not meet any of the yellow or red criteria but one of them meets the yellow criteria, the infrastructure light will be yellow.

this would be the same for red, if only one of the five rows meets the criteria it would be red.

Can I even do this on a Sheet? as it has to view multiple rows to determine a single indicator.

Also the due date thing indicator would be yellow if it is -1 to -4 days out from todays date and red if it is equal to or greater than todays date.

Thank you Paul for being so willing to work with my crazy!

Caroline

Paul Newcome

If it isn't weekly, then you would use a COUNTIFS in nested IFs.

=IF(COUNTIFS({Criteria}, @cell = "Infrastructure", {Due Date}, @cell<= TODAY())> 0, "Red", IF(.....

You would continue that out and adjust the number of days in the TODAY() function to determine your RYG.

delaurellc

Paul,

Thank you so much for hanging in there with me. I appreciate you taking the time to weed through my thought process. I think this will do it! I will come back and let you know how it goes!

Your the best!

Caroline

Paul Newcome

Happy to help. 👍️

NOTE: I just realized my example COUNTIFS above didn't include the done checkbox piece.

=IF(COUNTIFS({Criteria}, @cell = "Infrastructure", {Due Date}, @cell<= TODAY(), {Done}, @cell = 1)> 0, "Red", IF(.....

delaurellc

Hi Paul,

I know this is not right as I am getting unparseable error, can you help?

=IF({RADIO Log Range 4}==IF(COUNTIFS({Criteria Most Impacted}, @cell = "Infrastructure", {Due Date}, @cell<= TODAY(), {Done}, @cell = 1)> 0, "Red", IF(COUNTIFS({Criteria Most Impacted}, @cell = "Infrastructure", {Due Date}:{Due Date}, AND(IFERROR(@cell, TODAY(-1)) >= TODAY(), IFERROR(@cell, TODAY(-2)) <=TODAY())), {Done}, @cell = 1)> 0, "Yellow", Green))

I am assuming it is my referencing of the other table

Caroline

Paul Newcome

You don't need that section at the beginning, and you are missing quotes around the word Green.

=IF({RADIO Log Range 4}=

delaurellc

I don't understand, if I am telling the code to reference another sheet doesn't the reference have to be there? If I don't have the reference how does it know where to look?

Paul Newcome

It knows where to look based on what you select when you set up the cross sheet references within the COUNTIFS.

delaurellc

Sorry Paul,

I am truly lost, I have no reference to the sheet in the code anywhere, I only have reference to columns on the sheet. The sheet is called RADIO Log. So how would it know to go to the RADIO Log?

Caroline

Paul Newcome

When typing out the formula, anytime you see {Somethign like this} in curly brackets, it is a cross sheet reference. Following the belo steps should get you what you need.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

delaurellc

Thank you for trying still not helping. Have a good day!

delaurellc

Paul,

I am truly frustrated everything I try is not working and so I now don't know what to do. Thank you so much for trying to help me you have been great, I am just not getting it at all. Nothing I try is getting me any results and it should not be this hard to reference data.

The help info is not the best as they don't take into consideration complicated asks. My last try was this

=COUNTIF({RADIO Log Range 2}, {Criteria Most Impacted}, @cell = "Infrastructure", {Due Date}, @cell<= TODAY(), {Done}, @cell = 1)> 0, "Red", IF(COUNTIF({RADIO Log Range 2}, {Criteria Most Impacted}, @cell = "Infrastructure", {Due Date}:{Due Date}, AND(IFERROR(@cell, TODAY(-1)) >= TODAY(), IFERROR(@cell, TODAY(-2)) <=TODAY())), {Done}, @cell = 1)> 0, "Yellow", "Green")

Same Error

Thank you again! I am feeling pretty stupid right now!

Caroline