Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Nov 2008
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    I have a sheet with emloyee names on who have completed training sessions in the past. I have a second sheet where names are entered when they start training.
    I am linking the new names onto the old training sheet and need a "warning" when a name appears which has already been entered.
    This is problably easier to explain on the attached sheet (which is a very much shortened version)

    I would be grateful if someone could advise me on the best way of doing this

    Many thanks

    Sarah
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='sarahf' post='762773' date='02-Mar-2009 12:33'][/quote]
    I'm not sure I understand your question correctly, but perhaps the attached version does what you want.
    I modified the existing formulas slightly, and used a combination of formulas and conditional formatting in column D
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Nov 2008
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks Hans, that is just what i was after, apologies for the confused explanation!

  4. #4
    New Lounger
    Join Date
    Nov 2008
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans

    I have a complication in that the formula is working on some lines and not others.

    Have attached workbook once again with an example of a name which is not matching

    your help much appreciated
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    There's a space after "BARNES" in cell B7 but not in B6. Hence they are different.
    I'd check on the full name, by the way (column C or D), not on the last name only.

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='sarahf' post='762815' date='02-Mar-2009 15:16']I have a complication in that the formula is working on some lines and not others.[/quote]
    The name is not matching because it is DIFFERENT. One name is "Ian Barnes" and the other is "IAN BARNES ". If you need to do a compare that is case insensitive then you could try using VLookup.

    Try the formula =IF(ISERROR(VLOOKUP(D7,D$56,1)),"","Already Entered") in cell E7 of your latest workbook and see if that does what you want.

  7. #7
    New Lounger
    Join Date
    Nov 2008
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hi

    I can see what you mean about the additional space between names and this is causing a problem. I cant understand why its doing this as the full names are all created using the same formula.

    I have attached 3 of the names which are not working. Do you have any idea why an extra space is appearing? IF the space is taken out of the" " formula, then it works. I am mystified!

    Sarah
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Your workbook contains links to 'H:\Excel\[Manpower WHTerm February 200931 ver 1 (2).xls] west.h Sch 1', so we can't check the source. But apparently some of the names on that sheet have a trailing space. I have made the trailing spaces visible in columns C and D in the attached version (cells highlighted in green). Probably a data entry error.

    [attachment=82592:Woody_query_2.xls]
    Attached Files Attached Files

  9. #9
    New Lounger
    Join Date
    Nov 2008
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there any way of correcting this without re entering all the names? Very frustrating!

    Have attached a copy of the link if thats any help
    Attached Files Attached Files

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    [quote name='sarahf' post='763018' date='03-Mar-2009 06:36']Is there any way of correcting this without re entering all the names? Very frustrating![/quote]

    In E15 you can use something like:
    =trim(IF(D15="","",C15&" "&D15))

    to remove leading and trailing spaces as well as extra spaces

    Steve

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='sarahf' post='763018' date='03-Mar-2009 12:36']Is there any way of correcting this without re entering all the names? Very frustrating!

    Have attached a copy of the link if thats any help[/quote]
    You could run the following macro while the sheet with the problem data is open:
    Code:
    Sub Trimcells()
      Dim oCell As Range
      Application.Cursor = xlWait
      For Each oCell In ActiveSheet.UsedRange.Cells
    	If Application.IsText(oCell) And Not oCell.HasFormula Then
    	  oCell = Trim(oCell)
    	End If
      Next oCell
      Application.Cursor = xlDefault
    End Sub

  12. #12
    New Lounger
    Join Date
    Nov 2008
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    that seems to have done the trick

    many thanks

Posting Permissions

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