# This may be incredibly simple...

#### jondallimore

##### Board Regular
Hello,

I'm after a formula which would check that all the names on a list in column A are included in a range of data from B1:Z100 (might be different, but I can change it).

THe formula needs to return a "DONE" or "NOT DONE" - something like that.

Also, a red cell whilst its Not Done, and a Green when it is done would be useful - I would imagine conditional formatting would be the only way to do that?

This may be really simple, but I'm tired and my head is squishy from bashing it against excel all day...

Any help greatly appreciated.

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
To check on an individual cell basis, you would use:

Code:
``=IF(COUNTIF(A1,\$B\$1:\$Z\$100)>0,"DONE","NOT DONE")``

Sent me in the right direction. I've got this far:

=IF(SUMPRODUCT(COUNTIF(A1:X33,'Class Data'!A2:A30)),1,0)

This returns a 1 if ANY of the values in A2:A30 are in the range A1:X33

How could I change it to return a 1 only if ALL the values in A2:A30 are in the range A1:X33?

Thanks
Jon

Code:
``````Function FINDALL(source As Range, table As Range) As String

On Error GoTo errHandler

For Each cell In source
checkval = table.Find(cell.Value)
Next

FINDALL = "DONE"
Exit Function

errHandler:

FINDALL = "NOT DONE"

End Function``````

Here's a UDF that will get it done. Your source table (A2:A30) is the first entry. Then the A1:X33. Also, with your tables, A2:A30 will ALWAYS be in A1:X33 because...well...it's inclusive.

Code:
``=FINDALL(A2:A30,A1:X33)``

Hum. was hoping to avoid VBA.

I've never used it in that way before... I assume I just put the code into the sheet, and then the formula in the cell I want it in?

Ah, sorry, probably should have mentioned A2:A30 are on a different sheet - thought it was obvious from the formula I posted.

Replies
29
Views
1K
Replies
0
Views
198
Replies
1
Views
403
Replies
3
Views
565
Replies
7
Views
328

1,203,601
Messages
6,056,211
Members
444,850
Latest member
dancasta7

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back