Range with 2 validations
 

Dear experts,

I've a range named "staff" which I have set a validation: List -- source =
INDIRECT($B$1). For the dropdown list, the users can select the staff name
within the range. However, in the meantime, I want to set another validation
to the range so that there is no duplicate entry: Custom -- source =
COUNTIF($C$C, C2)<=1. However, when I select the range and want to set
another validation, the first validation appears and seems I cannot input
another validation. Is there a way to do this? If yes, how? If no, please
advise is there other way to achieve this.

Thanks in advance.

Range with 2 validations
 

It sounds like what you want to do is limit the selection so that any item
in the list can only be selected once.

See if this helps:

http://contextures.com/xlDataVal03.html

--
Biff
Microsoft Excel MVP


"Freshman" wrote in message
...
Dear experts,

I've a range named "staff" which I have set a validation: List -- source
=
INDIRECT($B$1). For the dropdown list, the users can select the staff name
within the range. However, in the meantime, I want to set another
validation
to the range so that there is no duplicate entry: Custom -- source =
COUNTIF($C$C, C2)<=1. However, when I select the range and want to set
another validation, the first validation appears and seems I cannot input
another validation. Is there a way to do this? If yes, how? If no, please
advise is there other way to achieve this.

Thanks in advance.

Range with 2 validations
 

Hi Biff,

I've read the link before but I just want to know whether I can use
validation to do this job and can Excel allows use to use 2 validation for
the same range.

Thanks & regards.

"T. Valko" wrote:

It sounds like what you want to do is limit the selection so that any item
in the list can only be selected once.

See if this helps:

http://contextures.com/xlDataVal03.html

--
Biff
Microsoft Excel MVP


"Freshman" wrote in message
...
Dear experts,

I've a range named "staff" which I have set a validation: List -- source
=
INDIRECT($B$1). For the dropdown list, the users can select the staff name
within the range. However, in the meantime, I want to set another
validation
to the range so that there is no duplicate entry: Custom -- source =
COUNTIF($C$C, C2)<=1. However, when I select the range and want to set
another validation, the first validation appears and seems I cannot input
another validation. Is there a way to do this? If yes, how? If no, please
advise is there other way to achieve this.

Thanks in advance.


.

Range with 2 validations
 

You can use only one type of validation at a time.

The info at that link is still using only one type of validation (list) but
how it's implemented it actually applies two types of validation, list and
only allow one unique selection per cell.

--
Biff
Microsoft Excel MVP


"Freshman" wrote in message
...
Hi Biff,

I've read the link before but I just want to know whether I can use
validation to do this job and can Excel allows use to use 2 validation for
the same range.

Thanks & regards.

"T. Valko" wrote:

It sounds like what you want to do is limit the selection so that any
item
in the list can only be selected once.

See if this helps:

http://contextures.com/xlDataVal03.html

--
Biff
Microsoft Excel MVP


"Freshman" wrote in message
...
Dear experts,

I've a range named "staff" which I have set a validation: List --
source
=
INDIRECT($B$1). For the dropdown list, the users can select the staff
name
within the range. However, in the meantime, I want to set another
validation
to the range so that there is no duplicate entry: Custom -- source =
COUNTIF($C$C, C2)<=1. However, when I select the range and want to set
another validation, the first validation appears and seems I cannot
input
another validation. Is there a way to do this? If yes, how? If no,
please
advise is there other way to achieve this.

Thanks in advance.


.

Range with 2 validations
 

Excel 2007 Validation List
List shrinks as names are used up.
http://www.mediafire.com/file/hgivly...11_14_09a.xlsm

Range with 2 validations
 

Excel 200 Validation List
Removed excess blanks.
http://c0444202.cdn.cloudfiles.racks...11_14_09a.xlsm