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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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