Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Unanswered: Data Validation Behaving Badly

    I have not used data validation before, and now when I try it out
    it does not pop any warning message. Can somebody please look
    at the attached and tell me what I am missing. My validation formula
    uses this in Cell A1:
    Code:
    =COUNTIF($A$1:$A$30,A1)>1
    then I copied the validation for a few rows, but without success.

    The real task is to pop up a warning when a duplicate Last Name, First Name,
    and Middle Initial are entered in three columns.
    I have Excel 2003.
    Thanks.
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    u need to think outside the square here
    have u used INDIRECT()

    take the >1 off the countif and make that coloumn hidden of white text.

    highlight the Area want to Condition Format goto to Condition Formatting

    change to Formula is

    =INDIRECT("???"&ROW())>1

    ??? is the Column you want to Check
    and then use the Format to change its that colour then the ROWs that are dup will be highlighted
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Thanks MYLE. I used data validation incorrectly with COUNTIF, in just trying to get
    data validation to work; I only selected one cell to create data validation, then did a
    copy an paste to the range I wanted to validate. I should have selected the whole
    range first.

    I saw how the INDIRECT formula can work in combination with conditional formatting
    and checking the concatenated First Name, Last Name, and Middle Initial.
    Solved

  4. #4
    Join Date
    Aug 2013
    Posts
    1
    =countif($A$1:$A$30,A1)=1
    No duplicate value entry with this validation formula...

Posting Permissions

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