| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation - restrict duplicate value
Hi!
How to set data validation function (in column B) to restrict user from entering same invoice number from same supplier? Column A: Supplier Column B: Supplier Invoice Number TIA |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data validation - restrict duplicate value
Using data validation you can; but only manual entries/dropdown entries will
be validated --Select B2:B10 --Select menu DataValidation --Select Custom from the 'Allow' dropdown and enter the below formula =COUNTIF($B$2:$B$10,B$2)=1 --From the Error alert tab enter the message you want to be displayed --Click OK and try. If you are selecting the entire column the validatio would be =COUNTIF(B:B,$B$1)=1 If this post helps click Yes --------------- Jacob Skaria "Michelle" wrote: Hi! How to set data validation function (in column B) to restrict user from entering same invoice number from same supplier? Column A: Supplier Column B: Supplier Invoice Number TIA |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data validation - restrict duplicate value
Correction to the formulas
=COUNTIF($B$2:$B$10,B2)=1 and for entire column =COUNTIF(B:B,B1)=1 If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Using data validation you can; but only manual entries/dropdown entries will be validated --Select B2:B10 --Select menu DataValidation --Select Custom from the 'Allow' dropdown and enter the below formula =COUNTIF($B$2:$B$10,B$2)=1 --From the Error alert tab enter the message you want to be displayed --Click OK and try. If you are selecting the entire column the validatio would be =COUNTIF(B:B,$B$1)=1 If this post helps click Yes --------------- Jacob Skaria "Michelle" wrote: Hi! How to set data validation function (in column B) to restrict user from entering same invoice number from same supplier? Column A: Supplier Column B: Supplier Invoice Number TIA |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data validation - restrict duplicate value
I interpret the post differently.
Supplier1...1 = ok Supplier2...1 = ok Supplier1...1 = not ok ..................1 = not ok Different suppliers having the same invoice number should be a valid entry. Although the chances of that happening might be pretty slim! Assume the range to validate is B2:B10 Select the range B2:B10 starting from cell B2 DataValidation Allow: Custom Formula: =AND($A2<"",SUMPRODUCT(--($A$2:$A2&"^^"&$B$2:B2=A2&"^^"&B2))<2) Uncheck Ignore blank OK -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Correction to the formulas =COUNTIF($B$2:$B$10,B2)=1 and for entire column =COUNTIF(B:B,B1)=1 If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Using data validation you can; but only manual entries/dropdown entries will be validated --Select B2:B10 --Select menu DataValidation --Select Custom from the 'Allow' dropdown and enter the below formula =COUNTIF($B$2:$B$10,B$2)=1 --From the Error alert tab enter the message you want to be displayed --Click OK and try. If you are selecting the entire column the validatio would be =COUNTIF(B:B,$B$1)=1 If this post helps click Yes --------------- Jacob Skaria "Michelle" wrote: Hi! How to set data validation function (in column B) to restrict user from entering same invoice number from same supplier? Column A: Supplier Column B: Supplier Invoice Number TIA |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data validation - restrict duplicate value
=AND($A2<"",SUMPRODUCT(--($A$2:$A2&"^^"&$B$2:B2=A2&"^^"&B2))<2)
And to be consistent, the column refs don't need to be absolute: =AND(A2<"",SUMPRODUCT(--(A$2:A2&"^^"&B$2:B2=A2&"^^"&B2))<2) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... I interpret the post differently. Supplier1...1 = ok Supplier2...1 = ok Supplier1...1 = not ok .................1 = not ok Different suppliers having the same invoice number should be a valid entry. Although the chances of that happening might be pretty slim! Assume the range to validate is B2:B10 Select the range B2:B10 starting from cell B2 DataValidation Allow: Custom Formula: =AND($A2<"",SUMPRODUCT(--($A$2:$A2&"^^"&$B$2:B2=A2&"^^"&B2))<2) Uncheck Ignore blank OK -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Correction to the formulas =COUNTIF($B$2:$B$10,B2)=1 and for entire column =COUNTIF(B:B,B1)=1 If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Using data validation you can; but only manual entries/dropdown entries will be validated --Select B2:B10 --Select menu DataValidation --Select Custom from the 'Allow' dropdown and enter the below formula =COUNTIF($B$2:$B$10,B$2)=1 --From the Error alert tab enter the message you want to be displayed --Click OK and try. If you are selecting the entire column the validatio would be =COUNTIF(B:B,$B$1)=1 If this post helps click Yes --------------- Jacob Skaria "Michelle" wrote: Hi! How to set data validation function (in column B) to restrict user from entering same invoice number from same supplier? Column A: Supplier Column B: Supplier Invoice Number TIA |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data validation - restrict duplicate value
thanks for your replies!
This is exactly what i want, sorry for not stating my question clearly in the first place =AND(A6<"",SUMPRODUCT(--(A$2:A6&"^^"&B$2:B6=A6&"^^"&B6))<2) valko, would you care to explain the formula used? "T. Valko" wrote: =AND($A2<"",SUMPRODUCT(--($A$2:$A2&"^^"&$B$2:B2=A2&"^^"&B2))<2) And to be consistent, the column refs don't need to be absolute: =AND(A2<"",SUMPRODUCT(--(A$2:A2&"^^"&B$2:B2=A2&"^^"&B2))<2) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... I interpret the post differently. Supplier1...1 = ok Supplier2...1 = ok Supplier1...1 = not ok .................1 = not ok Different suppliers having the same invoice number should be a valid entry. Although the chances of that happening might be pretty slim! Assume the range to validate is B2:B10 Select the range B2:B10 starting from cell B2 DataValidation Allow: Custom Formula: =AND($A2<"",SUMPRODUCT(--($A$2:$A2&"^^"&$B$2:B2=A2&"^^"&B2))<2) Uncheck Ignore blank OK -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Correction to the formulas =COUNTIF($B$2:$B$10,B2)=1 and for entire column =COUNTIF(B:B,B1)=1 If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Using data validation you can; but only manual entries/dropdown entries will be validated --Select B2:B10 --Select menu DataValidation --Select Custom from the 'Allow' dropdown and enter the below formula =COUNTIF($B$2:$B$10,B$2)=1 --From the Error alert tab enter the message you want to be displayed --Click OK and try. If you are selecting the entire column the validatio would be =COUNTIF(B:B,$B$1)=1 If this post helps click Yes --------------- Jacob Skaria "Michelle" wrote: Hi! How to set data validation function (in column B) to restrict user from entering same invoice number from same supplier? Column A: Supplier Column B: Supplier Invoice Number TIA . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Restrict Duplicates using Data Validation | Excel Discussion (Misc queries) | |||
| Data Validation - Restrict entry but without dropdown | Excel Discussion (Misc queries) | |||
| Data Validation /w Duplicate Entries | Excel Discussion (Misc queries) | |||
| Data Validation to restrict blank value in adjacent field | Excel Discussion (Misc queries) | |||
| Excel2000: Data Validation to restrict entries | Excel Discussion (Misc queries) | |||
Hybrid Mode
