Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique numbers entered once only
I am having a problem with a client using Excel to record receipts issued
to her clients. Looking at the data she has entered it appears that she has entered some receipts twice. Is there any way one can enforce unique integrity on a data column so that if the operator tries to enter the same number it will be rejected or ring warning bells ? If you can offer any suggestions it would be much appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique numbers entered once only
Hi,
Select column A and then Data|validation Select custom and enter this formula =COUNTIF($A:$A,A1)<2 Mike "Malcolm McMaster" wrote: I am having a problem with a client using Excel to record receipts issued to her clients. Looking at the data she has entered it appears that she has entered some receipts twice. Is there any way one can enforce unique integrity on a data column so that if the operator tries to enter the same number it will be rejected or ring warning bells ? If you can offer any suggestions it would be much appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique numbers entered once only
Could I barge in to ask a question on this? Mike, this works great. Could
you explain how this formula works in Data Validation? I don't get the A1 part. Thanks! James "Mike H" wrote in message ... Hi, Select column A and then Data|validation Select custom and enter this formula =COUNTIF($A:$A,A1)<2 Mike "Malcolm McMaster" wrote: I am having a problem with a client using Excel to record receipts issued to her clients. Looking at the data she has entered it appears that she has entered some receipts twice. Is there any way one can enforce unique integrity on a data column so that if the operator tries to enter the same number it will be rejected or ring warning bells ? If you can offer any suggestions it would be much appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique numbers entered once only
Hi
What it does in A1 is check whether the same value appears elsewhere in column A but because the whole column is selected when the formula is entered and because A1 isn't absolute it increments for each cell so the A2 formula becomes =COUNTIF($A:$A,A2)<2 This checks if the value in A2 appears in AA on so on. Got it? Mike "Zone" wrote: Could I barge in to ask a question on this? Mike, this works great. Could you explain how this formula works in Data Validation? I don't get the A1 part. Thanks! James "Mike H" wrote in message ... Hi, Select column A and then Data|validation Select custom and enter this formula =COUNTIF($A:$A,A1)<2 Mike "Malcolm McMaster" wrote: I am having a problem with a client using Excel to record receipts issued to her clients. Looking at the data she has entered it appears that she has entered some receipts twice. Is there any way one can enforce unique integrity on a data column so that if the operator tries to enter the same number it will be rejected or ring warning bells ? If you can offer any suggestions it would be much appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique numbers entered once only
Yes, I see. Thank you!
James "Mike H" wrote in message ... Hi What it does in A1 is check whether the same value appears elsewhere in column A but because the whole column is selected when the formula is entered and because A1 isn't absolute it increments for each cell so the A2 formula becomes =COUNTIF($A:$A,A2)<2 This checks if the value in A2 appears in AA on so on. Got it? Mike "Zone" wrote: Could I barge in to ask a question on this? Mike, this works great. Could you explain how this formula works in Data Validation? I don't get the A1 part. Thanks! James "Mike H" wrote in message ... Hi, Select column A and then Data|validation Select custom and enter this formula =COUNTIF($A:$A,A1)<2 Mike "Malcolm McMaster" wrote: I am having a problem with a client using Excel to record receipts issued to her clients. Looking at the data she has entered it appears that she has entered some receipts twice. Is there any way one can enforce unique integrity on a data column so that if the operator tries to enter the same number it will be rejected or ring warning bells ? If you can offer any suggestions it would be much appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique numbers entered once only
Malcolm,
You could try using Data Validation. Assuming the data to be entered will be in Column B you should click in the first row of that data and open the Data Validation Function from the Menubar and select Custom and then in the Formula field enter: =1=COUNTIF($B$1:$B$2000,B1) This assumes the data will go down as far as row 2000 and starts at row 1. (Change as required.) Then in the Error Alert tab of the Data Validation Dialog you should enter something like: This Invoice Number has already been entered! Make sure that the Style in this tab is selected to be Stop. When that part is completed, copy that cell and select all the cells below it and choose PasteSpecial from the menu Bar and click Validation. This will apply the Data Validation to all the cells you have selected in column B. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In spread sheet the numbers entered as whole numbers become decim | Excel Worksheet Functions | |||
how to extract unique numbers once from a list of repeated numbers? | Excel Discussion (Misc queries) | |||
All Numbers entered divided by 100,000,000,000! | Excel Discussion (Misc queries) | |||
How to format numbers that are already entered? | Excel Worksheet Functions | |||
Numbers entered change to 0 | Excel Discussion (Misc queries) |