Find text string in a value instead of in a formula?

[forteanajones]

What formula can I use to search a range of cell values for a specific text string? My method is searching formulas instead, which is not what I want.

Background: I am developing a little test script for a project and I want to include some automatic validations to help make the job of the testers easier.

What I have set up so far:

1. Testers input numbers into specific fields M8 through M20.
2. Fields N8 through N20 automatically check fields M8 through M20 and compare those with what is contained in a range of hidden cells I set up in advance. A formula ensures that it reports "correct" or "incorrect" on a field-by-field basis, based on the comparison.
3. Cell O8 searches for the text string "incorrect" in cells N8 through N20. If that text string appears in any one of the cell values (not formulas), then O8 directs the tester to test again. If the text string "incorrect" does not appear, then cell O8 reports that the test was successful, and the tester can proceed to the next test (exact same setup repeats).

My only problem is in the formula used for the last step. I have tried a few different formulas I know in cell 08, but none are giving me the results I'm looking for. The closest I have come is a successful search and validation based on the string being in the formula instead of in the value of the given cell.

 

[ADAMC]

Is this what your looking for:

=IF(N8="INCORRECT","TEST AGAIN",IF(N8="CORRECT","PROCEED TO NEXT TEST",""))

Place formula in O8 and fill down to o20

If N8 shows correct o8 will show test again, if N8 shows correct O8 will show proceed to next test, if N8 is blank O8 will show nothing?

 

[forteanajones]

Thanks! But I may not have been explicit or clear enough in my requirements.

Cells N8 through N20 are already doing the initial validations using a formula similar to the one you shared above. My problem is with cell 08 which is actually supposed to examine all cells from N8 through N20 for one or more failures, and report just once on the total success.

 

[ADAMC]

Okay...not quite sure what you actually want in O8?

Do you want cell 08 to show the number of incorrects?....Or corrects?...Am i even close (",) Report on the total success of what...

 

[forteanajones]

Okay...not quite sure what you actually want in O8?

Do you want cell 08 to show the number of incorrects?....Or corrects?...Am i even close (",) Report on the total success of what...

Here is a screenshot of my spreadsheet:

Cells N8 through N20 only test cells M8 through M20, while cell O8 alone tests to see if anything went wrong in the N column.

I believe the formula you suggested would be appropriate for the N column tests, not for the O column, unless I am misunderstanding you or the formula.

Thanks again, hopefully this is clearer.

Edited for spelling error.

 

[forteanajones]

For reference, the formula I am using for cells N8 through N20 is:

=IF(M8=L8,"Successful test", "Incorrect amount")

 

[ExcelChampion]

Maybe I'm missing something but:-

=IF(COUNTIF(N8:N20)>0,"INCORRECT","CORRECT")

Substitute Correct/Incorrectwith your text.

 

[forteanajones]

Here we go:

=IF(ISNUMBER(SEARCH("Successful",N8:N20)),"OK", "Not OK")

Edit: Whoops, looks like ExcelChampion came through as well. Thanks for the alternative solution!

 

[ExcelChampion]

I think mine would be more efficient.

 

[forteanajones]

I think mine would be more efficient.

Sorry, I had not seen your solution before I posted my latest formula.

Your formula definitely looks more elegant and efficient, but it is also relying on a numeric value when none exists in this case (I am looking for a specific text string). I could modify my N column to utilize numbers instead, but I prefer to keep things the way they are.

Thanks again!