Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

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

    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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Does the attached do what you want?
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Need an error-checking formula, please? (Excel

    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......

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts

    Re: Need an error-checking formula, please? (Excel

    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

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Need an error-checking formula, please? (Excel

    ...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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Need an error-checking formula, please? (Excel

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

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need an error-checking formula, please? (Excel

    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.
    Legare Coleman

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Need an error-checking formula, please? (Excel

    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.

  9. #9
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Need an error-checking formula, please? (Excel

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •