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

✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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)

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

Hi @Paul Newcome - that appears to do it!

Really appreciate the second set of eyes on this,

Jeff

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!