Help w/formula to look across cells to determine True or False

jmo

Hi folks - I have a sheet with over 100 columns and I need to look across the columns, that are all right next to each other, and determine if any of the cells has N/A or No - Fail in them.  There are 96 consecutive columns that I need to look across for that info and if the row cell has either N/A or No - Fail then mark the Defect column with a check mark.

I formatted the Defect column as check box and tried brute forcing the formula but I'm not having any success.

Start column name: Non-Disclosure Agreement (NDA)

End column name: All approvers listed in CLM approvals

96 total column to look in, including the start and end columns.

Formula I tried that isn't working:

=IF([Non-Disclosure Agreement (NDA)]@row:[All Approvers listed in CLM Approvals]@row, "N/A" OR "No - Fail", 1))

Appreciate any guidance you can provide.

Thanks,

Jeff

David Joyeuse

Hi @jmoser

Try this instead:

=IF(COUNTIFS([Non-Disclosure Agreement (NDA)]@row:[All Approvers listed in CLM Approvals]@row, OR(FIND("N/A", @cell)>1, FIND("No - Fail", @cell)>1))>1, 1, 0)

Hope it helped!

Paul Newcome

Try the adjustments I made to the FIND functions where we said greater than zero instead of greater than one. Then you are going to want to make sure that the two columns listed in the formula are the very first column to evaluate and the very last column to evaluate.

You say you have 90 columns... What are the different options that can be selected in each? I don't think you are reaching it, but you can't evaluate more than 4,000 characters in a string in a single cell. So let's just say each cell has 50 characters (including spaces) in it, You will only be able to evaluate the first 80 columns.

I doubt the character limit is the issue, but I am trying to cover all bases. Try the adjusted formula first.

David Joyeuse

Hi @jmoser

Try this instead:

=IF(COUNTIFS([Non-Disclosure Agreement (NDA)]@row:[All Approvers listed in CLM Approvals]@row, OR(FIND("N/A", @cell)>1, FIND("No - Fail", @cell)>1))>1, 1, 0)

Hope it helped!

jmo

Hi @David Joyeuse - I modified your formula just a tad because the way you typed yours still didn't make the Defect check mark when the row had N/A or No - Fail. I merely changed the greater than 1 to greater than 0 and it appeared to work fine:

=IF(COUNTIFS([Non-Disclosure Agreement (NDA)]@row:[All Approvers listed in CLM Approvals]@row, OR(FIND("N/A", @cell) > 0, FIND("No - Fail", @cell) > 0)) > 1, 1, 0)

Really appreciate your help on this!

Jeff

Paul Newcome

You are actually going to want your FIND functions to either be "greater than or equal to" 1 or "greater than" 0. Using "greater than 1" means that if "N/A" or "No - Fail" are the beginning of whatever text is in the cell, it won't be counted.

=IF(COUNTIFS([Non-Disclosure Agreement (NDA)]@row:[All Approvers listed in CLM Approvals]@row, OR(FIND("N/A", @cell)>0, FIND("No - Fail", @cell)>0))>1, 1, 0)

jmo

Hi @Paul Newcome - I put @David Joyeuse formula back into my Defect column but it still isn't marking the column when row data has an answer other than N/A or No - Fail:

=IF(COUNTIFS([Non-Disclosure Agreement (NDA)]@row:[All Approvers listed in CLM Approvals]@row, OR(FIND("N/A", @cell)>1, FIND("No - Fail", @cell)>1))>1, 1, 0)

I didn't show all 90+ columns but you get the gist - the NDA column is the start of the column string I'm looking over.

What am I missing or am I asking the wrong question?

Thanks,

Jeff

Paul Newcome

Try the adjustments I made to the FIND functions where we said greater than zero instead of greater than one. Then you are going to want to make sure that the two columns listed in the formula are the very first column to evaluate and the very last column to evaluate.

You say you have 90 columns... What are the different options that can be selected in each? I don't think you are reaching it, but you can't evaluate more than 4,000 characters in a string in a single cell. So let's just say each cell has 50 characters (including spaces) in it, You will only be able to evaluate the first 80 columns.

I doubt the character limit is the issue, but I am trying to cover all bases. Try the adjusted formula first.

jmo

Hi @Paul Newcome - that appears to do it!

Really appreciate the second set of eyes on this,

Jeff

jmo

https://community.smartsheet.com/discussion/comment/262021#Comment_262021

Also - we aren't anywhere near the character limit, the options were/are:

Yes - Pass

Yes - Defect Remediated

Yes - Defect Rebutted

No - Fail

N/A

Paul Newcome

Happy to help. 👍️

I doubted that it was the character limit, but figured I would go ahead and ask just in case the adjustments to the formula didn't work.

David Joyeuse

https://community.smartsheet.com/discussion/comment/262000#Comment_262000

For whatever reasons, I often mistakes on this... I feel dumb sometimes lol.