Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]() 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 |
#4
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]()
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 |
#5
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Repeating an entry | Excel Discussion (Misc queries) | |||
PIVOT LEARNER - Field Entry | Excel Worksheet Functions | |||
How to restrict entry or color the field | Excel Discussion (Misc queries) | |||
display Calendar for Excel field entry? | Excel Worksheet Functions | |||
help with macro to save repeating data entry | Excel Discussion (Misc queries) |