Help with creating a formula...

I have several worksheets that are in one workbook. One of the worksheets is
updated daily (We will enter in our foremen's initials if they are working).
I want Microsoft Excel to recognize when one of our foremen is not working.
I have created a list of the foremens names and the initials that are used.
I want Excel to see if the initials are in Column D. If they are NOT, I want
them to drop the initials AND name into column M. This is an effort to have
our information more readily available and accurate. Any help, would be
AWESOME!

I could send the file so that you know exactly what I mean...

Ok Meleah

I don't know how advanced your Excel is (form point of view of creating
names, formulae, adding modules etc...)

but here is some code that will do the trick, it's hard to do in a formula,
because a formula can tell against a whole list of names who is and who isn't
there, but it can't just show (in any way I know) only the people who are not
there...


Sub CheckNames()

Dim rngAllForemen As Excel.Range
'where all the foremen are named
Set rngAllForemen = Range("AllForemen")

Dim rngWorking As Excel.Range
Dim rngNotWorking As Excel.Range
Dim rngLoop As Excel.Range
Dim vntIsThere As Variant
Dim intI As Integer
'where the working ones are named
Set rngWorking = Range("B5").CurrentRegion
'you always need to do this...
Range("M5").CurrentRegion.Clear
Set rngNotWorking = Range("M5").CurrentRegion


'where the non-working ones are named



'always clear non-working ones first


For Each rngLoop In rngAllForemen
If Not IsNameInList(CStr(rngLoop.Value), rngWorking.Cells) Then
'an error means it wasn't there i.e match couldn't find it
rngNotWorking.Cells(intI, 1).Value = rngLoop
intI = intI + 1
End If
Next

End Sub


Function IsNameInList(strName As String, rngToSearch As Excel.Range) As
Boolean
Dim blnRetVal As Boolean
blnRetVal = False
Dim rngLoop As Excel.Range
For Each rngLoop In rngToSearch.Cells
If rngLoop.Value = strName Then
blnRetVal = True
Exit For
End If
Next

IsNameInList = blnRetVal


End Function





Assumptions Made:
that you create a reference list of the entire list of foremen and name it
"AllForemen"

That you start in B5 your first list of entries of foremen who are working
on that day

that the names of foremen (and or initials) are all in one cell


that you report the non-working formen starting from cell "M5"

that you don't have any columns either side of B or M as the currentregion
property the way it is used in the macro will wipe them out

that you hook up that code into a module and onto a button on the sheet
to do that you show the 'forms' toolbar (after you've pasted that code into
a module)
you drag a button onto the worksheet, Excel will prompt you with the 'assign
macro' dialog, and you choose 'CheckNames' as the macro for the button


in case of any doubt try this out on a copy of your data...not on the
original! please!
I don't want you to lose any data (remember undo is not straighforward to
implement in a macro), but also bear in mind the only thing that gets cleared
is the 'non-working' foremen list...


if you find this a bit too complicated, pls ignore and wait for a smarter
and more appropriate post :-)


Regards
CharlesA