How Nested IF can I get?

khankoff

I have a desire to change a Status column based on whether (or not) certain separate (other) columns are populated with a date.  Is this supposed to work?  All of the Nested IF examples I have seen tend to look at just a single column to evaluate against, and I'm wondering if I'm going to be able to make this work, and using what formula/approach.  I've attached an image from the worksheet.

Thanks in advance for any guidance. 

Mike Wilday

Can you share the formula attempts you have made? That will help us determine what is going on. 

khankoff

Here's where I am currently.  Seems over-engineered, and although not getting an error, I'm not accomplishing my objective:

 

=IF([Ready for InfoSec]1 <> "", "Transferring", IF([InfoSec Step 1 Start]1 <> "", "Preparing for Scan", IF([InfoSec Step 2 Start]1 <> "", "Decompressing Files", IF([InfoSec Step 3 Start]1 <> "", "Scanning in Progress", "Complete"))))

L_123

Wrote a bit more than I intended to. Disclaimer: Below is a intro to if statements in smartsheet, not a solution. You need to describe more about what your end objective is in order to provide a solution. Hopefully the below gives you the information you need in order to do so.

Stacked if statements are like a tree. Each if statement splits into 2 branches, a true one and a false one. Once you start on one branch, you cannot turn around. That means previous checks are still active, and the order you put the if statements in matters greatly. The first if statement is the trunk, then the thickest branches that hold the most weight, then the twigs at the ends of the branches.

=if(criteria, true, false)

=if(x > 0,"x > 0", "x < 0")

=if(x > 0, if(x > 100, "x > 100", "x < 0"), "x < 0")

=if(x>0, if(x>100,if(x<105,"100 < x < 105","x > 105"), "0 > x > 100"), "x < 0")

You can use if statements in diverse ways using almost any type of single cell criteria you can think of, and you can even use ranges when you combine if with other formulas. If statements are simple upfront but can become just as complex if not more than any other formula in Smartsheet, and can often be entire paragraphs of conditional logic, and they can almost always be reduced in complexity depending on your knowledge of smartsheet and conditional logic.

khankoff

Still haven't had anyone tell me definitively whether I should be able to evaluate against multiple column values in a single IF statement.  In other words, do I have to evaluate only what's in column A, or can I also evaluate (in the same formula) against Column A, Column B, Column F, etc.?

Mike Wilday

Hi Khankoff, You can measure against any number of columns. But once an IF statements is found to be true, it returns that specific statement's answer every time and doesn't evaluate the rest of the formula. 

IF this is true, THEN do this, <----- Evaluates first statement, finds it not to be true then moves on the to the next statement....

IF this is true, THEN do this... <----- Finds this true and returns answer. 

Sometimes you have to structure the IF statement backwards. Especially in cases where you are checking for data in multiple columns. Try structuring it to look at the last column first and go backward.

As you evaluate your failing IF statements, see if you determine which item is returning TRUE and keeping it from running. 

L_123

You can evaluate in any cell or all cell on that sheet, or even any cell on any sheet you have access to in a single statement.

Mike Wilday

The specific issue I see could be the problem with your if statement is that it appears that you are looking at your first column and determining if there is data in it or not. Once data gets populated there it will always return the first answer. Try what I suggested in reversing the order and see if it works for you. 

khankoff

Great guidance from all who responded.  Thank You.

If any of you are in attendance at Engage this year, look me up.

Ken

khankoff

It's working!

I'm as surprised as anyone :-)

Thanks again all.

=IF([InfoSec Step 4 Stop]@row <> "", "Complete", IF([InfoSec Step 4 Stop]@row <> "", "Complete", IF([InfoSec Step 3 Stop]@row <> "", "Evaluating Scan Results", IF([InfoSec Step 3 Start]@row <> "", "Deep Scanning", IF([InfoSec Step 2 Stop]@row <> "", "Preparing for Scan", IF([InfoSec Step 2 Start]@row <> "", "Decompressing Files", IF([InfoSec Step 1 Start]@row <> "", "Preparing Data", IF([Ready for InfoSec]@row <> "", "Ready for InfoSec", "Awaiting Green Light"))))))))