+ Reply to Thread
Results 1 to 50 of 50

validation rule

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    05-08-2005
    Location
    Australia
    MS-Off Ver
    excel 2019
    Posts
    112

    validation rule

    Hi - I have a roster in which in a range a1:a10, a15:a20 I will be putting names in.
    eg. andrew, charles, abdul, tony. Andrew or Charles, and abdul or tony can only occur once. how can I do a data validation on this.

    ie. andrew or charles can occur only once, abdul or tony can occur only once in the ranges

    A1:A10, A15:A20

    thanks

    andrewm

  2. #2
    Ron Coderre
    Guest

    RE: validation rule

    Try this:
    1)Select A1:A10 (with A1 as the active cell)
    2)Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$1:A1,A1)=1

    Then, with A15:A20 (A15 active)
    Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$15:A20,A15)=1

    Those will allow any entry in the lists, but no duplicates.

    Does that help?

    --
    Regards,
    Ron


  3. #3
    Jerry W. Lewis
    Guest

    Re: validation rule

    Custom validation with formula
    =COUNTIF($A$1:$A$10,A1)+COUNTIF($A$15:$A$20,A1)<2

    Jerry

    andrewm wrote:

    > Hi - I have a roster in which in a range a1:a10, a15:a20 I will be
    > putting names in.
    > eg. andrew, charles, abdul, tony. Andrew or Charles, and abdul or
    > tony can only occur once. how can I do a data validation on this.
    >
    > ie. andrew or charles can occur only once, abdul or tony can occur
    > only once in the ranges
    >
    > A1:A10, A15:A20
    >
    > thanks
    >
    > andrewm



  4. #4
    Forum Contributor
    Join Date
    05-08-2005
    Location
    Australia
    MS-Off Ver
    excel 2019
    Posts
    112
    Hi - the validation rule works but
    how do I make it specific for the names specified. Other names (eg John) can be entered in more than once.

    andrewm

  5. #5
    Debra Dalgleish
    Guest

    Re: validation rule

    You can create a list with the names, and the number of occurrences
    allowed. For example:

    John 2
    Tony 1

    Name this range, e.g. LookupList

    Then, in the data validation dialog box, use a formula that refers to
    this list. For example:

    =COUNTIF($B$1:$B$16,B1)<=VLOOKUP(B1,LookupList,2,0)

    andrewm wrote:
    > Hi - the validation rule works but
    > how do I make it specific for the names specified. Other names (eg
    > John) can be entered in more than once.
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  6. #6
    Forum Contributor
    Join Date
    05-08-2005
    Location
    Australia
    MS-Off Ver
    excel 2019
    Posts
    112
    Hi Team,

    unfortunately in the roster I have 2 ranges
    say
    the names - andrew can go in a1:a10, charles can go in a15:a20 but not both
    also tony can go in a1:10, abdul can go in a15:20, but not both

    any ideas

    andrewm

  7. #7
    Debra Dalgleish
    Guest

    Re: validation rule

    Perhaps, if you clearly outline all the rules, someone will be able to
    help you with a data validation formula.

    andrewm wrote:
    > Hi Team,
    >
    > unfortunately in the roster I have 2 ranges
    > say
    > the names - andrew can go in a1:a10, charles can go in a15:a20 but not
    > both
    > also tony can go in a1:10, abdul can go in a15:20, but not
    > both
    >
    > any ideas
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  8. #8
    Forum Contributor
    Join Date
    05-08-2005
    Location
    Australia
    MS-Off Ver
    excel 2019
    Posts
    112
    Sorry I'll try to explain what I need - in the roster there are two ranges in which a person's name is placed. However in one range their full name is placed and in the other range their initials are placed. there name can only by placed once (either their full name or their initials)
    (initials are used in one range as the cells are too small for their full name)

    ie.

    range 1. A1:A5 - names thus - joe blogs, tom jones
    range 2. c10:c20 - initials thus jb , tj

    joe blogs and jb are the same person, tom jones and tj are the same person.

    thanks

    andrewm

  9. #9
    Debra Dalgleish
    Guest

    Re: validation rule

    Perhaps, if you clearly outline all the rules, someone will be able to
    help you with a data validation formula.

    andrewm wrote:
    > Hi Team,
    >
    > unfortunately in the roster I have 2 ranges
    > say
    > the names - andrew can go in a1:a10, charles can go in a15:a20 but not
    > both
    > also tony can go in a1:10, abdul can go in a15:20, but not
    > both
    >
    > any ideas
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  10. #10
    Debra Dalgleish
    Guest

    Re: validation rule

    Perhaps, if you clearly outline all the rules, someone will be able to
    help you with a data validation formula.

    andrewm wrote:
    > Hi Team,
    >
    > unfortunately in the roster I have 2 ranges
    > say
    > the names - andrew can go in a1:a10, charles can go in a15:a20 but not
    > both
    > also tony can go in a1:10, abdul can go in a15:20, but not
    > both
    >
    > any ideas
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  11. #11
    Debra Dalgleish
    Guest

    Re: validation rule

    Perhaps, if you clearly outline all the rules, someone will be able to
    help you with a data validation formula.

    andrewm wrote:
    > Hi Team,
    >
    > unfortunately in the roster I have 2 ranges
    > say
    > the names - andrew can go in a1:a10, charles can go in a15:a20 but not
    > both
    > also tony can go in a1:10, abdul can go in a15:20, but not
    > both
    >
    > any ideas
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  12. #12
    Debra Dalgleish
    Guest

    Re: validation rule

    Perhaps, if you clearly outline all the rules, someone will be able to
    help you with a data validation formula.

    andrewm wrote:
    > Hi Team,
    >
    > unfortunately in the roster I have 2 ranges
    > say
    > the names - andrew can go in a1:a10, charles can go in a15:a20 but not
    > both
    > also tony can go in a1:10, abdul can go in a15:20, but not
    > both
    >
    > any ideas
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  13. #13
    Debra Dalgleish
    Guest

    Re: validation rule

    Perhaps, if you clearly outline all the rules, someone will be able to
    help you with a data validation formula.

    andrewm wrote:
    > Hi Team,
    >
    > unfortunately in the roster I have 2 ranges
    > say
    > the names - andrew can go in a1:a10, charles can go in a15:a20 but not
    > both
    > also tony can go in a1:10, abdul can go in a15:20, but not
    > both
    >
    > any ideas
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  14. #14
    Debra Dalgleish
    Guest

    Re: validation rule

    Perhaps, if you clearly outline all the rules, someone will be able to
    help you with a data validation formula.

    andrewm wrote:
    > Hi Team,
    >
    > unfortunately in the roster I have 2 ranges
    > say
    > the names - andrew can go in a1:a10, charles can go in a15:a20 but not
    > both
    > also tony can go in a1:10, abdul can go in a15:20, but not
    > both
    >
    > any ideas
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  15. #15
    Debra Dalgleish
    Guest

    Re: validation rule

    Perhaps, if you clearly outline all the rules, someone will be able to
    help you with a data validation formula.

    andrewm wrote:
    > Hi Team,
    >
    > unfortunately in the roster I have 2 ranges
    > say
    > the names - andrew can go in a1:a10, charles can go in a15:a20 but not
    > both
    > also tony can go in a1:10, abdul can go in a15:20, but not
    > both
    >
    > any ideas
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  16. #16
    Debra Dalgleish
    Guest

    Re: validation rule

    Perhaps, if you clearly outline all the rules, someone will be able to
    help you with a data validation formula.

    andrewm wrote:
    > Hi Team,
    >
    > unfortunately in the roster I have 2 ranges
    > say
    > the names - andrew can go in a1:a10, charles can go in a15:a20 but not
    > both
    > also tony can go in a1:10, abdul can go in a15:20, but not
    > both
    >
    > any ideas
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  17. #17
    Debra Dalgleish
    Guest

    Re: validation rule

    Perhaps, if you clearly outline all the rules, someone will be able to
    help you with a data validation formula.

    andrewm wrote:
    > Hi Team,
    >
    > unfortunately in the roster I have 2 ranges
    > say
    > the names - andrew can go in a1:a10, charles can go in a15:a20 but not
    > both
    > also tony can go in a1:10, abdul can go in a15:20, but not
    > both
    >
    > any ideas
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  18. #18
    Debra Dalgleish
    Guest

    Re: validation rule

    Perhaps, if you clearly outline all the rules, someone will be able to
    help you with a data validation formula.

    andrewm wrote:
    > Hi Team,
    >
    > unfortunately in the roster I have 2 ranges
    > say
    > the names - andrew can go in a1:a10, charles can go in a15:a20 but not
    > both
    > also tony can go in a1:10, abdul can go in a15:20, but not
    > both
    >
    > any ideas
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  19. #19
    Debra Dalgleish
    Guest

    Re: validation rule

    Perhaps, if you clearly outline all the rules, someone will be able to
    help you with a data validation formula.

    andrewm wrote:
    > Hi Team,
    >
    > unfortunately in the roster I have 2 ranges
    > say
    > the names - andrew can go in a1:a10, charles can go in a15:a20 but not
    > both
    > also tony can go in a1:10, abdul can go in a15:20, but not
    > both
    >
    > any ideas
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  20. #20
    Debra Dalgleish
    Guest

    Re: validation rule

    You can create a list with the names, and the number of occurrences
    allowed. For example:

    John 2
    Tony 1

    Name this range, e.g. LookupList

    Then, in the data validation dialog box, use a formula that refers to
    this list. For example:

    =COUNTIF($B$1:$B$16,B1)<=VLOOKUP(B1,LookupList,2,0)

    andrewm wrote:
    > Hi - the validation rule works but
    > how do I make it specific for the names specified. Other names (eg
    > John) can be entered in more than once.
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  21. #21
    Debra Dalgleish
    Guest

    Re: validation rule

    You can create a list with the names, and the number of occurrences
    allowed. For example:

    John 2
    Tony 1

    Name this range, e.g. LookupList

    Then, in the data validation dialog box, use a formula that refers to
    this list. For example:

    =COUNTIF($B$1:$B$16,B1)<=VLOOKUP(B1,LookupList,2,0)

    andrewm wrote:
    > Hi - the validation rule works but
    > how do I make it specific for the names specified. Other names (eg
    > John) can be entered in more than once.
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  22. #22
    Debra Dalgleish
    Guest

    Re: validation rule

    You can create a list with the names, and the number of occurrences
    allowed. For example:

    John 2
    Tony 1

    Name this range, e.g. LookupList

    Then, in the data validation dialog box, use a formula that refers to
    this list. For example:

    =COUNTIF($B$1:$B$16,B1)<=VLOOKUP(B1,LookupList,2,0)

    andrewm wrote:
    > Hi - the validation rule works but
    > how do I make it specific for the names specified. Other names (eg
    > John) can be entered in more than once.
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  23. #23
    Debra Dalgleish
    Guest

    Re: validation rule

    You can create a list with the names, and the number of occurrences
    allowed. For example:

    John 2
    Tony 1

    Name this range, e.g. LookupList

    Then, in the data validation dialog box, use a formula that refers to
    this list. For example:

    =COUNTIF($B$1:$B$16,B1)<=VLOOKUP(B1,LookupList,2,0)

    andrewm wrote:
    > Hi - the validation rule works but
    > how do I make it specific for the names specified. Other names (eg
    > John) can be entered in more than once.
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  24. #24
    Debra Dalgleish
    Guest

    Re: validation rule

    You can create a list with the names, and the number of occurrences
    allowed. For example:

    John 2
    Tony 1

    Name this range, e.g. LookupList

    Then, in the data validation dialog box, use a formula that refers to
    this list. For example:

    =COUNTIF($B$1:$B$16,B1)<=VLOOKUP(B1,LookupList,2,0)

    andrewm wrote:
    > Hi - the validation rule works but
    > how do I make it specific for the names specified. Other names (eg
    > John) can be entered in more than once.
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  25. #25
    Debra Dalgleish
    Guest

    Re: validation rule

    You can create a list with the names, and the number of occurrences
    allowed. For example:

    John 2
    Tony 1

    Name this range, e.g. LookupList

    Then, in the data validation dialog box, use a formula that refers to
    this list. For example:

    =COUNTIF($B$1:$B$16,B1)<=VLOOKUP(B1,LookupList,2,0)

    andrewm wrote:
    > Hi - the validation rule works but
    > how do I make it specific for the names specified. Other names (eg
    > John) can be entered in more than once.
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  26. #26
    Debra Dalgleish
    Guest

    Re: validation rule

    You can create a list with the names, and the number of occurrences
    allowed. For example:

    John 2
    Tony 1

    Name this range, e.g. LookupList

    Then, in the data validation dialog box, use a formula that refers to
    this list. For example:

    =COUNTIF($B$1:$B$16,B1)<=VLOOKUP(B1,LookupList,2,0)

    andrewm wrote:
    > Hi - the validation rule works but
    > how do I make it specific for the names specified. Other names (eg
    > John) can be entered in more than once.
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  27. #27
    Debra Dalgleish
    Guest

    Re: validation rule

    You can create a list with the names, and the number of occurrences
    allowed. For example:

    John 2
    Tony 1

    Name this range, e.g. LookupList

    Then, in the data validation dialog box, use a formula that refers to
    this list. For example:

    =COUNTIF($B$1:$B$16,B1)<=VLOOKUP(B1,LookupList,2,0)

    andrewm wrote:
    > Hi - the validation rule works but
    > how do I make it specific for the names specified. Other names (eg
    > John) can be entered in more than once.
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  28. #28
    Debra Dalgleish
    Guest

    Re: validation rule

    You can create a list with the names, and the number of occurrences
    allowed. For example:

    John 2
    Tony 1

    Name this range, e.g. LookupList

    Then, in the data validation dialog box, use a formula that refers to
    this list. For example:

    =COUNTIF($B$1:$B$16,B1)<=VLOOKUP(B1,LookupList,2,0)

    andrewm wrote:
    > Hi - the validation rule works but
    > how do I make it specific for the names specified. Other names (eg
    > John) can be entered in more than once.
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  29. #29
    Debra Dalgleish
    Guest

    Re: validation rule

    You can create a list with the names, and the number of occurrences
    allowed. For example:

    John 2
    Tony 1

    Name this range, e.g. LookupList

    Then, in the data validation dialog box, use a formula that refers to
    this list. For example:

    =COUNTIF($B$1:$B$16,B1)<=VLOOKUP(B1,LookupList,2,0)

    andrewm wrote:
    > Hi - the validation rule works but
    > how do I make it specific for the names specified. Other names (eg
    > John) can be entered in more than once.
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  30. #30
    Debra Dalgleish
    Guest

    Re: validation rule

    You can create a list with the names, and the number of occurrences
    allowed. For example:

    John 2
    Tony 1

    Name this range, e.g. LookupList

    Then, in the data validation dialog box, use a formula that refers to
    this list. For example:

    =COUNTIF($B$1:$B$16,B1)<=VLOOKUP(B1,LookupList,2,0)

    andrewm wrote:
    > Hi - the validation rule works but
    > how do I make it specific for the names specified. Other names (eg
    > John) can be entered in more than once.
    >
    > andrewm
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  31. #31
    Ron Coderre
    Guest

    RE: validation rule

    Try this:
    1)Select A1:A10 (with A1 as the active cell)
    2)Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$1:A1,A1)=1

    Then, with A15:A20 (A15 active)
    Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$15:A20,A15)=1

    Those will allow any entry in the lists, but no duplicates.

    Does that help?

    --
    Regards,
    Ron


  32. #32
    Ron Coderre
    Guest

    RE: validation rule

    Try this:
    1)Select A1:A10 (with A1 as the active cell)
    2)Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$1:A1,A1)=1

    Then, with A15:A20 (A15 active)
    Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$15:A20,A15)=1

    Those will allow any entry in the lists, but no duplicates.

    Does that help?

    --
    Regards,
    Ron


  33. #33
    Jerry W. Lewis
    Guest

    Re: validation rule

    Custom validation with formula
    =COUNTIF($A$1:$A$10,A1)+COUNTIF($A$15:$A$20,A1)<2

    Jerry

    andrewm wrote:

    > Hi - I have a roster in which in a range a1:a10, a15:a20 I will be
    > putting names in.
    > eg. andrew, charles, abdul, tony. Andrew or Charles, and abdul or
    > tony can only occur once. how can I do a data validation on this.
    >
    > ie. andrew or charles can occur only once, abdul or tony can occur
    > only once in the ranges
    >
    > A1:A10, A15:A20
    >
    > thanks
    >
    > andrewm



  34. #34
    Jerry W. Lewis
    Guest

    Re: validation rule

    Custom validation with formula
    =COUNTIF($A$1:$A$10,A1)+COUNTIF($A$15:$A$20,A1)<2

    Jerry

    andrewm wrote:

    > Hi - I have a roster in which in a range a1:a10, a15:a20 I will be
    > putting names in.
    > eg. andrew, charles, abdul, tony. Andrew or Charles, and abdul or
    > tony can only occur once. how can I do a data validation on this.
    >
    > ie. andrew or charles can occur only once, abdul or tony can occur
    > only once in the ranges
    >
    > A1:A10, A15:A20
    >
    > thanks
    >
    > andrewm



  35. #35
    Ron Coderre
    Guest

    RE: validation rule

    Try this:
    1)Select A1:A10 (with A1 as the active cell)
    2)Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$1:A1,A1)=1

    Then, with A15:A20 (A15 active)
    Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$15:A20,A15)=1

    Those will allow any entry in the lists, but no duplicates.

    Does that help?

    --
    Regards,
    Ron


  36. #36
    Jerry W. Lewis
    Guest

    Re: validation rule

    Custom validation with formula
    =COUNTIF($A$1:$A$10,A1)+COUNTIF($A$15:$A$20,A1)<2

    Jerry

    andrewm wrote:

    > Hi - I have a roster in which in a range a1:a10, a15:a20 I will be
    > putting names in.
    > eg. andrew, charles, abdul, tony. Andrew or Charles, and abdul or
    > tony can only occur once. how can I do a data validation on this.
    >
    > ie. andrew or charles can occur only once, abdul or tony can occur
    > only once in the ranges
    >
    > A1:A10, A15:A20
    >
    > thanks
    >
    > andrewm



  37. #37
    Ron Coderre
    Guest

    RE: validation rule

    Try this:
    1)Select A1:A10 (with A1 as the active cell)
    2)Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$1:A1,A1)=1

    Then, with A15:A20 (A15 active)
    Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$15:A20,A15)=1

    Those will allow any entry in the lists, but no duplicates.

    Does that help?

    --
    Regards,
    Ron


  38. #38
    Ron Coderre
    Guest

    RE: validation rule

    Try this:
    1)Select A1:A10 (with A1 as the active cell)
    2)Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$1:A1,A1)=1

    Then, with A15:A20 (A15 active)
    Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$15:A20,A15)=1

    Those will allow any entry in the lists, but no duplicates.

    Does that help?

    --
    Regards,
    Ron


  39. #39
    Jerry W. Lewis
    Guest

    Re: validation rule

    Custom validation with formula
    =COUNTIF($A$1:$A$10,A1)+COUNTIF($A$15:$A$20,A1)<2

    Jerry

    andrewm wrote:

    > Hi - I have a roster in which in a range a1:a10, a15:a20 I will be
    > putting names in.
    > eg. andrew, charles, abdul, tony. Andrew or Charles, and abdul or
    > tony can only occur once. how can I do a data validation on this.
    >
    > ie. andrew or charles can occur only once, abdul or tony can occur
    > only once in the ranges
    >
    > A1:A10, A15:A20
    >
    > thanks
    >
    > andrewm



  40. #40
    Jerry W. Lewis
    Guest

    Re: validation rule

    Custom validation with formula
    =COUNTIF($A$1:$A$10,A1)+COUNTIF($A$15:$A$20,A1)<2

    Jerry

    andrewm wrote:

    > Hi - I have a roster in which in a range a1:a10, a15:a20 I will be
    > putting names in.
    > eg. andrew, charles, abdul, tony. Andrew or Charles, and abdul or
    > tony can only occur once. how can I do a data validation on this.
    >
    > ie. andrew or charles can occur only once, abdul or tony can occur
    > only once in the ranges
    >
    > A1:A10, A15:A20
    >
    > thanks
    >
    > andrewm



  41. #41
    Ron Coderre
    Guest

    RE: validation rule

    Try this:
    1)Select A1:A10 (with A1 as the active cell)
    2)Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$1:A1,A1)=1

    Then, with A15:A20 (A15 active)
    Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$15:A20,A15)=1

    Those will allow any entry in the lists, but no duplicates.

    Does that help?

    --
    Regards,
    Ron


  42. #42
    Ron Coderre
    Guest

    RE: validation rule

    Try this:
    1)Select A1:A10 (with A1 as the active cell)
    2)Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$1:A1,A1)=1

    Then, with A15:A20 (A15 active)
    Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$15:A20,A15)=1

    Those will allow any entry in the lists, but no duplicates.

    Does that help?

    --
    Regards,
    Ron


  43. #43
    Jerry W. Lewis
    Guest

    Re: validation rule

    Custom validation with formula
    =COUNTIF($A$1:$A$10,A1)+COUNTIF($A$15:$A$20,A1)<2

    Jerry

    andrewm wrote:

    > Hi - I have a roster in which in a range a1:a10, a15:a20 I will be
    > putting names in.
    > eg. andrew, charles, abdul, tony. Andrew or Charles, and abdul or
    > tony can only occur once. how can I do a data validation on this.
    >
    > ie. andrew or charles can occur only once, abdul or tony can occur
    > only once in the ranges
    >
    > A1:A10, A15:A20
    >
    > thanks
    >
    > andrewm



  44. #44
    Jerry W. Lewis
    Guest

    Re: validation rule

    Custom validation with formula
    =COUNTIF($A$1:$A$10,A1)+COUNTIF($A$15:$A$20,A1)<2

    Jerry

    andrewm wrote:

    > Hi - I have a roster in which in a range a1:a10, a15:a20 I will be
    > putting names in.
    > eg. andrew, charles, abdul, tony. Andrew or Charles, and abdul or
    > tony can only occur once. how can I do a data validation on this.
    >
    > ie. andrew or charles can occur only once, abdul or tony can occur
    > only once in the ranges
    >
    > A1:A10, A15:A20
    >
    > thanks
    >
    > andrewm



  45. #45
    Ron Coderre
    Guest

    RE: validation rule

    Try this:
    1)Select A1:A10 (with A1 as the active cell)
    2)Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$1:A1,A1)=1

    Then, with A15:A20 (A15 active)
    Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$15:A20,A15)=1

    Those will allow any entry in the lists, but no duplicates.

    Does that help?

    --
    Regards,
    Ron


  46. #46
    Jerry W. Lewis
    Guest

    Re: validation rule

    Custom validation with formula
    =COUNTIF($A$1:$A$10,A1)+COUNTIF($A$15:$A$20,A1)<2

    Jerry

    andrewm wrote:

    > Hi - I have a roster in which in a range a1:a10, a15:a20 I will be
    > putting names in.
    > eg. andrew, charles, abdul, tony. Andrew or Charles, and abdul or
    > tony can only occur once. how can I do a data validation on this.
    >
    > ie. andrew or charles can occur only once, abdul or tony can occur
    > only once in the ranges
    >
    > A1:A10, A15:A20
    >
    > thanks
    >
    > andrewm



  47. #47
    Ron Coderre
    Guest

    RE: validation rule

    Try this:
    1)Select A1:A10 (with A1 as the active cell)
    2)Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$1:A1,A1)=1

    Then, with A15:A20 (A15 active)
    Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$15:A20,A15)=1

    Those will allow any entry in the lists, but no duplicates.

    Does that help?

    --
    Regards,
    Ron


  48. #48
    Ron Coderre
    Guest

    RE: validation rule

    Try this:
    1)Select A1:A10 (with A1 as the active cell)
    2)Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$1:A1,A1)=1

    Then, with A15:A20 (A15 active)
    Data>Validation
    Allow: Custom
    Formula: =COUNTIF(A$15:A20,A15)=1

    Those will allow any entry in the lists, but no duplicates.

    Does that help?

    --
    Regards,
    Ron


  49. #49
    Jerry W. Lewis
    Guest

    Re: validation rule

    Custom validation with formula
    =COUNTIF($A$1:$A$10,A1)+COUNTIF($A$15:$A$20,A1)<2

    Jerry

    andrewm wrote:

    > Hi - I have a roster in which in a range a1:a10, a15:a20 I will be
    > putting names in.
    > eg. andrew, charles, abdul, tony. Andrew or Charles, and abdul or
    > tony can only occur once. how can I do a data validation on this.
    >
    > ie. andrew or charles can occur only once, abdul or tony can occur
    > only once in the ranges
    >
    > A1:A10, A15:A20
    >
    > thanks
    >
    > andrewm



  50. #50
    Jerry W. Lewis
    Guest

    Re: validation rule

    Custom validation with formula
    =COUNTIF($A$1:$A$10,A1)+COUNTIF($A$15:$A$20,A1)<2

    Jerry

    andrewm wrote:

    > Hi - I have a roster in which in a range a1:a10, a15:a20 I will be
    > putting names in.
    > eg. andrew, charles, abdul, tony. Andrew or Charles, and abdul or
    > tony can only occur once. how can I do a data validation on this.
    >
    > ie. andrew or charles can occur only once, abdul or tony can occur
    > only once in the ranges
    >
    > A1:A10, A15:A20
    >
    > thanks
    >
    > andrewm



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1