This may be incredibly simple...

[jondallimore]

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.

 

[NeonRedSharpie]

To check on an individual cell basis, you would use:

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

 

[jondallimore]

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

 

[NeonRedSharpie]

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.

=FINDALL(A2:A30,A1:X33)

 

[jondallimore]

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?

 

[jondallimore]

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