Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
3 data validations
Hi
I have data in column A, B and C (these are account numbers). I need to prevent a) duplicate entries - which is working fine with "COUNTIF($A:$C,A2)<2" formula in custom data validation I also need to add two more validations b) only numbers are allowed (no letters or characters) c) the lenght must be 8 Can some one help me with a formula for all 3 of them or is there a better way of doing this in VBA (instead of data validation) Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
3 data validations
=AND(COUNTIF(:A:$C,A2)<2,ISNUMBER(A2),LEN(A2)=8)
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mohan" wrote in message ... Hi I have data in column A, B and C (these are account numbers). I need to prevent a) duplicate entries - which is working fine with "COUNTIF($A:$C,A2)<2" formula in custom data validation I also need to add two more validations b) only numbers are allowed (no letters or characters) c) the lenght must be 8 Can some one help me with a formula for all 3 of them or is there a better way of doing this in VBA (instead of data validation) Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
3 data validations
Thanks.. It worked perfectly! (just a typo on :A.. s/b $A)
"Bob Phillips" wrote: =AND(COUNTIF(:A:$C,A2)<2,ISNUMBER(A2),LEN(A2)=8) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mohan" wrote in message ... Hi I have data in column A, B and C (these are account numbers). I need to prevent a) duplicate entries - which is working fine with "COUNTIF($A:$C,A2)<2" formula in custom data validation I also need to add two more validations b) only numbers are allowed (no letters or characters) c) the lenght must be 8 Can some one help me with a formula for all 3 of them or is there a better way of doing this in VBA (instead of data validation) Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing Data | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Saving data in a worksheet within a workbook | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel |