Nested if(or( refering to another sheet range

Ian Macintosh

Hi all,

Gentle here please, newbee to this stuff :-)

I am trying to refer to another sheet where I have a risk matrix (usual thing; likelihood on one matrix and consequence on the other).

The sheet I am referring to is called "Reference". Here's what I am trying from my main sheet:

=IF([Overall Compliance]@row = "Not Applicable", "Not Applicable", IFERROR(IF(OR(ISBLANK(Likelihood@row), ISBLANK(Consequence@row)), "", INDEX({Reference Range 1}, MATCH(Likelihood@row, {Reference Range 1}, 0), MATCH(Consequence@row, {Reference Range 1}, 0))), "Error"))

I know this works in Google sheets (I am trying to convert a sheet over). Obviously the syntax is a little different (sheet names, Number/row nomenclature) but I feel it should be ok.

CLEARLY I am doing something wrong as it comes up as unparsable.

The first bit works fine: =IF([Overall Compliance]@row = "Not Applicable", "Not Applicable") on it's own, so my stuff up must be in the second section.

I would appreciate any tips on how to fix.

Ian

Kelly Moore

Hey @Ian Macintosh

Unfortunately, what you're trying to do isn't possible, as written. We won't be using your reference sheet. We have to tell smartsheet with a series of nested IFs, what those intersections mean. This formula will look a lot worse than it is. It uses a combination of OR and ANDs in a repetitive sequence. Since your Overall Risk is always dependent on the response in the Likelihood column AND Consequence column, produyou have a series of AND pairs. However, a number of those pairs will produce the same final answer. That is the OR portion of the formula. Because your 5x5 risk matrix is producing so many choices, your formula has many IFs, ANDs or ORs.

This formula would go into your Overall Risk column. If your Likelihood and Consequence columns are not already formatted as Single choice dropdowns, please make them so. You will probably also want to restrict the choices to the dropdown list.

=IF(OR(AND(Consequence@row = "Insignificant", Likelihood@row = "Remote"), AND(Consequence@row = "Minor", Likelihood@row = "Remote"), AND(Consequence@row = "Moderate", Likelihood@row = "Remote"), AND(Consequence@row = "Insignificant", Likelihood@row = "Unlikely"), AND(Consequence@row = "Minor", Likelihood@row = "Unlikely"), AND(Consequence@row = "Insignificant", Likelihood@row = "Possible")), "Low", IF(OR(AND(Consequence@row = "Major", Likelihood@row = "Remote"), AND(Consequence@row = "Moderate", Likelihood@row = "Unlikely"), AND(Consequence@row = "Major", Likelihood@row = "Unlikely"), AND(Consequence@row = "Minor", Likelihood@row = "Possible"), AND(Consequence@row = "Moderate", Likelihood@row = "Possible"), AND(Consequence@row = "Insignificant", Likelihood@row = "Likely"), AND(Consequence@row = "Minor", Likelihood@row = "Likely"), AND(Consequence@row = "Insignificant", Likelihood@row = "Almost Certain")), "Moderate", IF(OR(AND(Consequence@row = "Severe", Likelihood@row = "Remote"), AND(Consequence@row = "Severe", Likelihood@row = "Unlikely"), AND(Consequence@row = "Severe", Likelihood@row = "Possible"), AND(Consequence@row = "Major", Likelihood@row = "Possible"), AND(Consequence@row = "Moderate", Likelihood@row = "Likely"), AND(Consequence@row = "Major", Likelihood@row = "Likely"), AND(Consequence@row = "Minor", Likelihood@row = "Almost Certain"), AND(Consequence@row = "Moderate", Likelihood@row = "Almost Certain")), "High", IF(OR(AND(Consequence@row = "Severe", Likelihood@row = "Likely"), AND(Consequence@row = "Major", Likelihood@row = "Almost Certain"), AND(Consequence@row = "Severe", Likelihood@row = "Almost Certain")), "Extreme"))))

Like I said, it looks worse than it is because of the number of choices from this 5x5 risk matrix.

more info on IF/AND see below. The IF/OR follows the same syntax

https://help.smartsheet.com/function/and

Kelly Moore

Hey @Ian Macintosh

I just noticed my last comments and final version of formula didn't post. That happens sometimes when I refresh my comments prior to posting and it posts the last draft instead.

In the final version, I added the condition that both the Consequence and Likelihood couldn't be blank. I also shortened the formula - I can do this because you have a finite list of conditions. You'll note that the Moderate category isn't spelled out - it says if it isn't any of the others it has to be Moderate.

=IF(AND(Likelihood@row <> "", Consequence@row <> ""), IF(OR(AND(Consequence@row = "Insignificant", Likelihood@row = "Remote"), AND(Consequence@row = "Minor", Likelihood@row = "Remote"), AND(Consequence@row = "Moderate", Likelihood@row = "Remote"), AND(Consequence@row = "Insignificant", Likelihood@row = "Unlikely"), AND(Consequence@row = "Minor", Likelihood@row = "Unlikely"), AND(Consequence@row = "Insignificant", Likelihood@row = "Possible")), "Low", IF(OR(AND(Consequence@row = "Severe", Likelihood@row = "Remote"), AND(Consequence@row = "Severe", Likelihood@row = "Unlikely"), AND(Consequence@row = "Severe", Likelihood@row = "Possible"), AND(Consequence@row = "Major", Likelihood@row = "Possible"), AND(Consequence@row = "Moderate", Likelihood@row = "Likely"), AND(Consequence@row = "Major", Likelihood@row = "Likely"), AND(Consequence@row = "Minor", Likelihood@row = "Almost Certain"), AND(Consequence@row = "Moderate", Likelihood@row = "Almost Certain")), "High", IF(OR(AND(Consequence@row = "Severe", Likelihood@row = "Likely"), AND(Consequence@row = "Major", Likelihood@row = "Almost Certain"), AND(Consequence@row = "Severe", Likelihood@row = "Almost Certain")), "Extreme", "Moderate"))))

cheers,

Kelly

Ian Macintosh

Thought this might help. Its an image of the matrix table:

I need to return the intersection value from the table. Eg if the consequence is moderate and the likelihood is Likely then the risk level to return to the main sheet is "High"

Kelly Moore

Hey @Ian Macintosh

Unfortunately, what you're trying to do isn't possible, as written. We won't be using your reference sheet. We have to tell smartsheet with a series of nested IFs, what those intersections mean. This formula will look a lot worse than it is. It uses a combination of OR and ANDs in a repetitive sequence. Since your Overall Risk is always dependent on the response in the Likelihood column AND Consequence column, produyou have a series of AND pairs. However, a number of those pairs will produce the same final answer. That is the OR portion of the formula. Because your 5x5 risk matrix is producing so many choices, your formula has many IFs, ANDs or ORs.

This formula would go into your Overall Risk column. If your Likelihood and Consequence columns are not already formatted as Single choice dropdowns, please make them so. You will probably also want to restrict the choices to the dropdown list.

=IF(OR(AND(Consequence@row = "Insignificant", Likelihood@row = "Remote"), AND(Consequence@row = "Minor", Likelihood@row = "Remote"), AND(Consequence@row = "Moderate", Likelihood@row = "Remote"), AND(Consequence@row = "Insignificant", Likelihood@row = "Unlikely"), AND(Consequence@row = "Minor", Likelihood@row = "Unlikely"), AND(Consequence@row = "Insignificant", Likelihood@row = "Possible")), "Low", IF(OR(AND(Consequence@row = "Major", Likelihood@row = "Remote"), AND(Consequence@row = "Moderate", Likelihood@row = "Unlikely"), AND(Consequence@row = "Major", Likelihood@row = "Unlikely"), AND(Consequence@row = "Minor", Likelihood@row = "Possible"), AND(Consequence@row = "Moderate", Likelihood@row = "Possible"), AND(Consequence@row = "Insignificant", Likelihood@row = "Likely"), AND(Consequence@row = "Minor", Likelihood@row = "Likely"), AND(Consequence@row = "Insignificant", Likelihood@row = "Almost Certain")), "Moderate", IF(OR(AND(Consequence@row = "Severe", Likelihood@row = "Remote"), AND(Consequence@row = "Severe", Likelihood@row = "Unlikely"), AND(Consequence@row = "Severe", Likelihood@row = "Possible"), AND(Consequence@row = "Major", Likelihood@row = "Possible"), AND(Consequence@row = "Moderate", Likelihood@row = "Likely"), AND(Consequence@row = "Major", Likelihood@row = "Likely"), AND(Consequence@row = "Minor", Likelihood@row = "Almost Certain"), AND(Consequence@row = "Moderate", Likelihood@row = "Almost Certain")), "High", IF(OR(AND(Consequence@row = "Severe", Likelihood@row = "Likely"), AND(Consequence@row = "Major", Likelihood@row = "Almost Certain"), AND(Consequence@row = "Severe", Likelihood@row = "Almost Certain")), "Extreme"))))

Like I said, it looks worse than it is because of the number of choices from this 5x5 risk matrix.

more info on IF/AND see below. The IF/OR follows the same syntax

https://help.smartsheet.com/function/and

Ian Macintosh

Wow! As you say - it looks bad, but if that's the way to go the, WAY TO GO!!

Thank you for the feedback your time and experience are appreciated.

Ian

Kelly Moore

Hey @Ian Macintosh

I just noticed my last comments and final version of formula didn't post. That happens sometimes when I refresh my comments prior to posting and it posts the last draft instead.

In the final version, I added the condition that both the Consequence and Likelihood couldn't be blank. I also shortened the formula - I can do this because you have a finite list of conditions. You'll note that the Moderate category isn't spelled out - it says if it isn't any of the others it has to be Moderate.

=IF(AND(Likelihood@row <> "", Consequence@row <> ""), IF(OR(AND(Consequence@row = "Insignificant", Likelihood@row = "Remote"), AND(Consequence@row = "Minor", Likelihood@row = "Remote"), AND(Consequence@row = "Moderate", Likelihood@row = "Remote"), AND(Consequence@row = "Insignificant", Likelihood@row = "Unlikely"), AND(Consequence@row = "Minor", Likelihood@row = "Unlikely"), AND(Consequence@row = "Insignificant", Likelihood@row = "Possible")), "Low", IF(OR(AND(Consequence@row = "Severe", Likelihood@row = "Remote"), AND(Consequence@row = "Severe", Likelihood@row = "Unlikely"), AND(Consequence@row = "Severe", Likelihood@row = "Possible"), AND(Consequence@row = "Major", Likelihood@row = "Possible"), AND(Consequence@row = "Moderate", Likelihood@row = "Likely"), AND(Consequence@row = "Major", Likelihood@row = "Likely"), AND(Consequence@row = "Minor", Likelihood@row = "Almost Certain"), AND(Consequence@row = "Moderate", Likelihood@row = "Almost Certain")), "High", IF(OR(AND(Consequence@row = "Severe", Likelihood@row = "Likely"), AND(Consequence@row = "Major", Likelihood@row = "Almost Certain"), AND(Consequence@row = "Severe", Likelihood@row = "Almost Certain")), "Extreme", "Moderate"))))

cheers,

Kelly

Ian Macintosh

Awesome. Thanks :-)

Ian