Hello,

I would like to check that the contents of a single cell contain all of the contents from a number of other cells. Here is an example.Sheet 1 A1 = 111]222]333

Sheet 2 A5 = r4]r5

Sheet 3 A1 = 111]222]333]r4]r5in Sheet 4 cell A1 the formula is basically going to say if sheet3!A1 contains the contents of Sheet1!A1 and the contents of Sheet2!A5 then "pass" else "FAIL"

This formula will be copied from column to column in sheet four so that the relative column reference keeps moving. So Sheet4 B1 says if Sheet3!B1 = Sheet1!B1 and Sheet2!B5 and so on and so forth.

Thank you for looking at this issue

Dranoel

Hi, Put this formula in A1, then drag as required:

=IF(AND(Sheet1!A1="",Sheet2!A1="",Sheet3!A1=""),"",IF(Sheet3!A1="","Empty", IF(AND(FIND(Sheet1!A1,Sheet3!A1,1)>0, FIND(Sheet2!A1,Sheet3!A1,1)>0),"Pass","Fail")))Note that the formula has been split onto three lines for ease of viewing. Reassemble into one line for use as a formula.

If the cell in sheet 3 is empty but there is something in the relevant cell in sheet 1 and/or sheet 2 this formula returns "Empty" as a warning. (You can change this to Fail if preferred).

If the cells in sheets 1, 2 and 3 are all empty the formula returns nothing "".Regards

Hi,

Sorry for the delay in responding but I got moved away from this project for a while.When I use the code. I started out with a blank sheet. In that sheet I placed this much of the code in A1. The idea is that the position on sheet 1 matches the position on sheet 2, Sheet two is the original data, sheet three and four contain the converted data. So data in sheet 2 a1 will be in sheet 3 a40. I want to see pass or fail on sheet 1 a1.

=IF(FIND(SHEET2!A1,SHEET3!A40,1)>0,"PASS","FAIL")

and I receive an error stating that one of the data types is invalid. Both cells are formatted as text. any idea why there is a data type error?

sheet 2 A1 = U1882296

sheet 3 A40 = e1234, f445566, U1882296right click, format cells, both show text as content type.

Hi, When do you get this error.

Is it when you enter the formula or when you enter data on Sheet 2 or Sheet3.

Formulas return errors such as #VALUE or #NA, so I am not sure when this 'one of the data types is invalid' appears.

The formula and data you posted actually worked when I entered the formula on Sheet1 and the data in A1 and A40 on sheets 2 & 3.

This formula needs to be:

=IF(ISERROR(FIND(Sheet2!A1,Sheet3!A40,1)),"FAIL","PASS")

as FIND returns #VALUE if no match is found.So try that instead.

Regards

Thank you very much, all I needed was the ISERROR. I thought that the zero would be returned if find failed. Thanks again, I really appreciate your help.

Dranoel

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History