RE: DataValidation Check in cell of excel

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.

.

Relevant Pages

  • Dynamic PreparedStatements with Variable In Parameters
    ... AND col2=? ... AND col3=? ... I have to start coding tomorrow so I'm eager ...
    (comp.lang.java.programmer)
  • Re: Strange effects of Cast
    ... Cast to tuncate strings. ... In the query below although I get the expected result in COL1 and COL2 ... COL3 and COL4 return only three characters. ...
    (comp.databases.oracle.misc)
  • Re: How to check that the Cursor is on the last row?
    ... That is, in Erland's example no two rows have the same col1, same ... ORDER BY col1, col2 DESC, col3, PKcol ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.server)
  • Re: slecting multiple distinct columns..
    ... this will bring back records that have duplicate records ... Col1, Col2, Col3, COUNTas NumberDups ...
    (comp.databases.ms-sqlserver)
  • sql queries
    ... Case When c.ColumnName = 'col1' Then t.col1 ... When c.ColumnName = 'col2' Then t.col2 ... Select 'col3') c ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.server)