Help needed on multple formulas in data validation

[raghavender]

Hi,

Please help me in datavalidation forumla

I trying to add multiple conditions in custom formula

1) I wanted to prevent the duplicate entries in range of cells (=COUNTIF($C$20:$C$31,C20)=1)

2) In same range of cells, data should accept from another range.

I tried to add both formulas using AND formula, but not wokring.

Can some body help on this

Thanks

 

[T. Valko]

Hi,

Please help me in datavalidation forumla

I trying to add multiple conditions in custom formula

1) I wanted to prevent the duplicate entries in range of cells (=COUNTIF($C$20:$C$31,C20)=1)

2) In same range of cells, data should accept from another range.

I tried to add both formulas using AND formula, but not wokring.

Can some body help on this

Thanks

Can you provide more specific details with regards to item 2?

 

[raghavender]

Ok sir, i will try to explain in detail:

In datavalidation option i need multiple conditions to be applied
1) Duplicate entries to be prevented, for that i tried to add the formula as (=countif($c$20:$c$31,c20)=1) and it is working fine.
2) Second conditions is : in cell range c20 to c31 values should match in another table,
a. For this i have defined a table with name “xxxxx”
b. i just added a custom validation rule as =named range i.e. =xxxxx.
Validations are working fine when i applied individually, i need both validations to be worked,
For this i added the custom validation as “ =and(countif($c$20:$c$31,c20)=1,xxxxx).
But both conditions are not working, please help me is there any way that multiple conditions can be applied in data validation.

Please reply me if you are not clear with questions.

Thanks in advance and appreciate your help on this.

Raghu

 

[T. Valko]

Ok sir, i will try to explain in detail:

In datavalidation option i need multiple conditions to be applied
1) Duplicate entries to be prevented, for that i tried to add the formula as (=countif($c$20:$c$31,c20)=1) and it is working fine.
2) Second conditions is : in cell range c20 to c31 values should match in another table,
a. For this i have defined a table with name “xxxxx”
b. i just added a custom validation rule as =named range i.e. =xxxxx.
Validations are working fine when i applied individually, i need both validations to be worked,
For this i added the custom validation as “ =and(countif($c$20:$c$31,c20)=1,xxxxx).
But both conditions are not working, please help me is there any way that multiple conditions can be applied in data validation.

Please reply me if you are not clear with questions.

Thanks in advance and appreciate your help on this.

Raghu

Try this...

=AND(COUNTIF($C$20:$C$31,C20)=1,COUNTIF(xxxxx,C20))

 

[raghavender]

Wow , its working perfectly fine, thanks for your swift response.

Is there any way that i can also add a "drop down list" data validation rule from another range.

Thanks,
Raghu

 

[T. Valko]

Wow , its working perfectly fine, thanks for your swift response.

Is there any way that i can also add a "drop down list" data validation rule from another range.

Thanks,
Raghu

Not in the same range. That would be 2 different types of validation and you can only have 1 type of validation for a cell.

You can probably do what you want with a VBA event macro if you can get someone to write the code.