Need an error-checking formula, please? (Excel 2003; Win XP)

[dmcnab]

Hi all--I am looking for a formula that will read text (eg: a series of names) in one cell (eg: B2) and compare it to a list of names in a range (eg: D2:J2) and if any of the names in B2 are found in the range D2:J2, the formula will return an indication of a conflict or error in cell C2....the indication of ERROR can be the word "ERROR" or (better yet) a value which indicates the number of names that are 'duplicated'.....I have attached a small sample...thanks

[LegareColeman]

Does the attached do what you want?

[dmcnab]

Thanks Legare...it works quite nicely, but having seen that I am now wondering if it is possible to more precisely identify where the 'errors' are....I am attaching another version of what you sent me......I would like to be able to show how many errors per employee there are in the top row above each person's name?....and, possibly, set it up so that the cell(s) having errors change color (eg: white on red) to identify the row that contains the error (to make it easier to track it down and correct it).....I am attaching my second s/sheet......

[sdckapr]

HOw about with conditional formatting:
Select D3:J16
Format - conditional formatting...
Formula is:
=AND(NOT(ISBLANK(D3)),ISNUMBER(SEARCH(D3,$B3)))
[format]
Font (tab) Color white
Patterns(tab) color red
[Ok][OK]

Steve

[dmcnab]

...my sincere apologies to Coleman & Steve........I wasn't thinking when I assembled the sample & didn't set it up right.....(probably been working on it too ling & need a rest..[img]/forums/images/smilies/smile.gif[/img]...).........I am attaching the correct sample of what I am trying to do.....for purposes of illustration, I have colored cells red where there is a conflict b/w a work assignment and some other 'assignment'........so, I begin again, asking for a formula that will compare names in the cells in col B with the names in the rows in the range K3:Q16 (which will be hidden) and list the number of 'errors' (ie: conflicts) in the cell(s) in col C.......and, auto-shade with red-on-white the cells in D3:J16 where an error exists so that it can be readily found and changed.......if the 'number' of errors per employee could show in the row above their names, that would be great, but it's not that important so long as the auto-coloring occurs....sorry for the initial mix-up....thank you for your patience and help

[HansV]

Formula for conditional formatting (with D3:J16 selected):
<code>
=AND(NOT(K3=""),ISNUMBER(SEARCH(K3,$B3)))</code>

[LegareColeman]

What you are asking now will be difficult to do and have it update automatically. Why not just set up the sheet like the attached which eliminates the possibility of the type of error you are talking about.

[HansV]

Formula for C3:
<code>
=SUMPRODUCT((NOT(K3:Q3="")*ISNUMBER(SEARCH(K3:Q3,$ B3))))
</code>
and fill down to C16.
Formula for D1:
<code>
=SUMPRODUCT((NOT(K3:K16="")*ISNUMBER(SEARCH(K3:K16 ,$B3:$B16))))
</code>
and fill right to J1.

[dmcnab]

....thanks Hans, Steve, Legare---your help was invaluable, and your patience very much appreciated.....thanks again.