there are 3 excel cols,

when user enters in col1, it should be allowed only if data is not
present in col2 and col3.

also when enters in col2, not be allowed to enter if data is there in
col1 and col3

also when enters in col3, not be allowed to enter is data is there in
col1 and col2

so which is the formula to use in the excel col so that the above
holds true.

Selecting your 3 cells (assuming A2:C2)
Data-Validation, custom. Formula is:
=COUNTA($A2:$C2)<=1

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"AmitAgarwal" wrote:

there are 3 excel cols,

when user enters in col1, it should be allowed only if data is not
present in col2 and col3.

also when enters in col2, not be allowed to enter if data is there in
col1 and col3

also when enters in col3, not be allowed to enter is data is there in
col1 and col2

so which is the formula to use in the excel col so that the above
holds true.

Hi,

The answer depends on what you actually mean by the question. If you only
want one entry per row then use Luke's suggestion or the slightly shorter
version

=COUNTA($A2:$C2)=1

But if you mean there can be no duplicates on a row then use

You can prevent duplicate entries in a range as follows:

1. Highlight the range, lets say A1:C1
2. Choose the command Data, Validation
3. Under Allow choose Custom
4. Enter the following formula in the Formulas box:
=COUNTIF($A1:$C1,A1)=1

One thing to keep in mind - if the user copies and pastes data into the
range where the Data Validation is, it is wiped out and anything can be
entered.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"AmitAgarwal" wrote:

there are 3 excel cols,

when user enters in col1, it should be allowed only if data is not
present in col2 and col3.

also when enters in col2, not be allowed to enter if data is there in
col1 and col3

also when enters in col3, not be allowed to enter is data is there in
col1 and col2

so which is the formula to use in the excel col so that the above
holds true.

Thanks luke as well Shane for your prmpt replies,

it worked ,

thanks again


On Jul 17, 7:52*pm, Shane Devenshire
wrote:
Hi,

The answer depends on what you actually mean by the question. *If you only
want one entry per row then use Luke's suggestion or the slightly shorter
version

=COUNTA($A2:$C2)=1

But if you mean there can be no duplicates on a row then use

You can prevent duplicate entries in a range as follows:

1. Highlight the range, lets say A1:C1
2. Choose the command Data, Validation
3. Under Allow choose Custom
4. Enter the following formula in the Formulas box:
=COUNTIF($A1:$C1,A1)=1

One thing to keep in mind - if the user copies and pastes data into the
range where the Data Validation is, it is wiped out and anything can be
entered.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire



"AmitAgarwal" wrote:
there are 3 excel cols,

when user enters in col1, it should be allowed only if data is not
present in col2 and col3.

also when enters in col2, not be allowed to enter if data is there in
col1 and col3

also when enters in col3, not be allowed to enter is data is there in
col1 and col2

so which is the formula to use in the excel col so that the above
holds true.- Hide quoted text -

- Show quoted text -