This may be incredibly simple...

jondallimore

Board Regular
Joined
Apr 26, 2012
Messages
136
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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
To check on an individual cell basis, you would use:

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

jondallimore

Board Regular
Joined
Apr 26, 2012
Messages
136
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

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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)
 

jondallimore

Board Regular
Joined
Apr 26, 2012
Messages
136
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

Board Regular
Joined
Apr 26, 2012
Messages
136
Ah, sorry, probably should have mentioned A2:A30 are on a different sheet - thought it was obvious from the formula I posted.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,956
Messages
5,525,883
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top