Range with 2 validations
in [Functions]
|
From: Freshman on 12 Nov 2009 21:59 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.
From: T. Valko on 12 Nov 2009 22:20 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" <Freshman(a)discussions.microsoft.com> wrote in message news:43AE97FD-FEDE-4EBB-85C0-D930B7132199(a)microsoft.com... > 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.
From: Freshman on 14 Nov 2009 09:59 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" <Freshman(a)discussions.microsoft.com> wrote in message > news:43AE97FD-FEDE-4EBB-85C0-D930B7132199(a)microsoft.com... > > 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. > > > . >
From: T. Valko on 14 Nov 2009 11:55 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" <Freshman(a)discussions.microsoft.com> wrote in message news:66F4C692-57B9-4BCF-B734-DACB93F580DD(a)microsoft.com... > 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" <Freshman(a)discussions.microsoft.com> wrote in message >> news:43AE97FD-FEDE-4EBB-85C0-D930B7132199(a)microsoft.com... >> > 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. >> >> >> . >>
From: Herbert Seidenberg on 14 Nov 2009 23:03
Excel 2007 Validation List List shrinks as names are used up. http://www.mediafire.com/file/hgivlyz21iz/11_14_09a.xlsm |