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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,982
Members
430,100
Latest member
namhnz

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top