Results 1 to 4 of 4

DATA VALIDATION

This is a discussion on DATA VALIDATION within the Excel Questions forums, part of the Question Forums category; I am trying to use the Data /Validation function to show a warning message when a duplicate number is input ...

  1. #1
    New Member
    Join Date
    Aug 2002
    Posts
    14

    Default DATA VALIDATION

    I am trying to use the Data /Validation function to show a warning message when a duplicate number is input into the current cell(i.e F4), which matches a number which could have been input into a cell within the range (F8:F5000).

    Please could you advise if such a function is possible in Data validation, and if so the correct syntax to use.

    Alternatively is there any other function that could achieve the desired result.

    Thanks

  2. #2
    Board Regular
    Join Date
    Jun 2002
    Posts
    84

    Default

    You could setup a formula within another part of the spreadsheet that vlookups the number in F4, if it finds it then enter a 0 in the box, if not then enter a one. This can be done using the if(iserror( statements.

    Have a macro that works upon changing that call (F4), you may need help for this - I dont know how to do this.

    That macro could look at the vlookup result, if it is a 1 then it does nothing, if it is a 0 then put a msgbox up, and if necessary clear F4 out.

    This should work in theory, but I am unsure of the code to do so.

    Hope it helps.
    stuart

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default

    =ISNA(MATCH(F4,F8:F5000,0))

  4. #4
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default

    =COUNTIF($F$8:$F$5000,F4)=0

    If the value entered in F4 is in the range F8:F5000, you will recieve the warning message

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com