example:

A1 = "car" B1 =
A2 = "lorry" B2 =
A3 = "tractor" B3 =
A4 = "bus" B4 =
A5 = "tractor" B5 = "this item has already been entered"


I need a formula to be entered into col"B" to give the message described.
Any suggestion?

Ta

Try this in B1, and copy down as needed:

=IF(COUNTIF($A$1:A1,A1)1,"This item has already been entered","")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Pat" wrote in message
...
example:

A1 = "car" B1 =
A2 = "lorry" B2 =
A3 = "tractor" B3 =
A4 = "bus" B4 =
A5 = "tractor" B5 = "this item has already been entered"


I need a formula to be entered into col"B" to give the message described.
Any suggestion?

Ta

Hi!

If you enter the data from the top down all the time:

Leave B1 empty. A1 is the top cell so nothing could have
been "already entered", right?

In B2 enter this formula and copy down as needed:

=IF(COUNTIF(A$1:A2,A2)1,"This item has already been
entered","")

Biff

-----Original Message-----
example:

A1 = "car" B1 =
A2 = "lorry" B2 =
A3 = "tractor" B3 =
A4 = "bus" B4 =
A5 = "tractor" B5 = "this item has already been entered"


I need a formula to be entered into col"B" to give the
message described.
Any suggestion?

Ta


.

Hi

You can use custom data validation, to restrict non-unicue entries into
range. When an attempt to enter some value repeatedly, an error message (you
can design it yourself, or leave the standard message unchanged) pops up,
the user is informed about input error, and he/she is allowed to choose or
to retry or to cancel.

P.e. Select the range A1:A100 and then from Data menu Validation. Set Allow:
to Custom, and into Formula field enter
=COUNTIF(A:A,A1)<2
Activate Error Alert tab, and into Title field enter something like:
Input Error!
Into Error Message field enter something like:
Only unique entries are allowed! This entry already exist in column A!
Press OK

Now try to enter any double entry into range A1:A100.

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Pat" wrote in message
...
example:

A1 = "car" B1 =
A2 = "lorry" B2 =
A3 = "tractor" B3 =
A4 = "bus" B4 =
A5 = "tractor" B5 = "this item has already been entered"


I need a formula to be entered into col"B" to give the message described.
Any suggestion?

Ta