My manager has this query. He is entering the stock details of his phamacy.
He has 6 columns. One is "name of the distributor",
the second is "bill number or invoice number",
the third is "amount to be paid",
the fourth is "cheque number"
the fifth is whether it has been "paid" or is "unpaid". The lst coumn I
have given conditional formatting, so that if it is paid, it will show the
fonts in green and if it is unpaid, it will show the fonts in red.

Now our query is that after say about 50 rows of enteries made, he would
like to know the moment he enters the bill no in the second coulmn, if excel
can tell that this bill no has been enetered before? Like when you enter text
excel repeats the entry so you can come to know, but it does not do that in
number data type. I tried entering the numbers as a text data type, but no
use ?

Dr Alok Modi MD

You could use either Conditional Formatting or Data Validation:

Conditional Formatting:
=COUNTIF(B:B,$B$3)1

Data Validation:
=COUNTIF(B:B,B2)=1
and check ignore blanks

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dr Alok Modi MD" wrote in message
...
My manager has this query. He is entering the stock details of his
phamacy.
He has 6 columns. One is "name of the distributor",
the second is "bill number or invoice number",
the third is "amount to be paid",
the fourth is "cheque number"
the fifth is whether it has been "paid" or is "unpaid". The lst coumn I
have given conditional formatting, so that if it is paid, it will show the
fonts in green and if it is unpaid, it will show the fonts in red.

Now our query is that after say about 50 rows of enteries made, he would
like to know the moment he enters the bill no in the second coulmn, if
excel
can tell that this bill no has been enetered before? Like when you enter
text
excel repeats the entry so you can come to know, but it does not do that
in
number data type. I tried entering the numbers as a text data type, but no
use ?

Dr Alok Modi MD

Hi Sandy
Thanks for replying to me. My problem is different here. See suppose I am
entering various invoice numbers in coumn B. Let us say I have entered the
following 10 invoice numbers :
2342
323
121212
12445
323423
435432
23432
23233
234
2332

No let us say i am about to enter 23432, in (now the cell would be B11),
B11, excel would not tell me that 23432 has been eneterd already. imagine how
this gets complicated if already say 100 enteries have been made. Now when we
enter text instaed of numbers, the moment you repeat text excel enters it
automatically and highlights it. So you know that you have laready entered it.

Can you provide me a similar solution od a solution by which I can get an
error dialogue flash ?

Dr Alok Modi MD

Try this ..

Select col B
Click Data Validation
Allow: Custom
Formula: =COUNTIF(B:B,B1)<2
Click OK
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dr Alok Modi MD" wrote:

Hi Sandy
Thanks for replying to me. My problem is different here. See suppose I am
entering various invoice numbers in coumn B. Let us say I have entered the
following 10 invoice numbers :
2342
323
121212
12445
323423
435432
23432
23233
234
2332

No let us say i am about to enter 23432, in (now the cell would be B11),
B11, excel would not tell me that 23432 has been eneterd already. imagine how
this gets complicated if already say 100 enteries have been made. Now when we
enter text instaed of numbers, the moment you repeat text excel enters it
automatically and highlights it. So you know that you have laready entered it.

Can you provide me a similar solution od a solution by which I can get an
error dialogue flash ?

Dr Alok Modi MD

The Data Validation or Conditional Formatting do not come into action until
the Enter key is pressed. In fact there are no functions or user
programmable actions including VBA which are available while the cell is in
edit mode.

Unless I am not understanding you correctly, even text will not Autocomplete
until XL can "guess" what the entry is going to be. So even if you prefixed
an alpha character to make the entry text, with say, A1234, A1235 already
entered, you will not get any highlighting even when you have entered A123
even it you then enter a 4 to make it a duplicate entry.

The best option that I can see is to use Conditional Formatting which will
highlight both the entry that you have just made and the other duplicate
entry. At least it would if I had suggested the correct formula of:

=COUNTIF(B:B,B3)1

in Cell B3


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dr Alok Modi MD" wrote in message
...

Hi Sandy
Thanks for replying to me. My problem is different here. See suppose I am
entering various invoice numbers in coumn B. Let us say I have entered the
following 10 invoice numbers :
2342
323
121212
12445
323423
435432
23432
23233
234
2332

No let us say i am about to enter 23432, in (now the cell would be B11),
B11, excel would not tell me that 23432 has been eneterd already. imagine
how
this gets complicated if already say 100 enteries have been made. Now when
we
enter text instaed of numbers, the moment you repeat text excel enters it
automatically and highlights it. So you know that you have laready entered
it.

Can you provide me a similar solution od a solution by which I can get an
error dialogue flash ?

Dr Alok Modi MD