Data validation with 3 Criteria's already have data form

[vsaenz]

I have been working on this for a bit now...watched videos on all of these type of criteria but i cant seem to find one with all three.
I have a database with contacts, basic name, phone number date called and notes. What I'm trying to do is avoid duplicates when the data entry is
being inputted by multiple users of course. I understand the COUNTIF formula, and the fact that there are millions of rows so I cant put unlimited rows so I added an end range.
What I'm having trouble with is the whole number formula, with the data validation custom I add the AND then I get stuck with the LEN formula...because I input no more than
10 characters for a phone number. However, I don't like the row to just be numbers I want it to have dashes. I originally thought that if I eliminate all the dashes first then add the formula...then I could add the column format numbers but then what happened was is showed up as error because there were more than 10 characters.

Sorry if i am all over the place so let me make it clear what I'm trying to do
1. COUNTIF($C$2:$C$63,C2)=1
2. LEN(C2)<10
together do i input the equals sign first then the AND? then add both formulas like so....
=ANDCOUNTIF($C$2:$C$63,C2)=1,LEN(C2)<10

So I've tried this and i get errors on all rows i removed the LEN and get no errors but i need the limit of characters to avoid adding more numbers to the cell.
My very last question is i tried checking the COUNTIF formula and it works by itself but only until the 63 row...it lets me add duplicate past that row that's why i need it to be a future number like 999999 but it gives me an error when i do that.

Help me please...i already have a crm for other types of things but this needs to be very basic...entry data available for my agent to log in and input new numbers and call those that are on their and add notes.
I don't understand macros or vba but I've looked into them and it just seems like it gives me more than I need. I'm just looking for something simple. Yet i know I'm making it complicated with this one column i need 3 validation for.

So to be clear the 3 criteria is to continue this with additional rows being inputted no limit.
THANK YOU SO MUCH!

 

[vladimiratanasiu]

okay i tried it and its giving me an error?‍

"There's a problem with this formula. Not trying to type a formula? when the first character is an = or minus -..excel thinks its a formula"...blah blah blah
any suggestions?

This error is due to number format defined in your PC regional settings . Try to replace inside the formula(s), the comma with semicolon character(s) (e.g. =LEFT(K3;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A3&"0123456789"))-1))

 

[DanteAmor]

what about the FALSE word do i add that at the end so that if it input data doesn't follow this

Remember, the formula goes into data validation, it does not require "False"
__________________________________________________________________

okay so this is how i would change it by inputting the starting cell C2 all the way to lets say C300? this will avoid duplicates
the LEN formula will avoid no more than 10 characters in each cell and the ISNUMBER will avoid any letters only numbers.
Is that correct?

Correct.
__________________________________________________________________

i could also use C:C range too right thats what that means....?

=AND(ISNUMBER(C2),LEN(C2)=10,COUNTIF(C2:C300,C2)=1)

Help me with your request, I can't follow you, first you want the whole column, now a rank.
__________________________________________________________________

oh and what about the dollar signs do i need those added anywhere?

[QUOTE="vsaenz, post: 5729590, member: 481663"]
=AND(ISNUMBER(C2),LEN(C2)=10,COUNTIF($C$2:$C$300,C2)=1)
[/QUOTE]

__________________________________________________________________
Did I miss something?

 

[vsaenz]

Remember, the formula goes into data validation, it does not require "False"
__________________________________________________________________

Correct.
__________________________________________________________________


Help me with your request, I can't follow you, first you want the whole column, now a rank.
__________________________________________________________________

[QUOTE="vsaenz, post: 5729590, member: 481663"]
=AND(ISNUMBER(C2),LEN(C2)=10,COUNTIF($C$2:$C$300,C2)=1)
[/QUOTE]

__________________________________________________________________
Did I miss something?

no you didnt miss anything....you basically answered my question about adding the word false...and once i explained the formula back to you...you also agreed that it was correct.
I only want the whole column...sorry dont know any rank stuff but i know that i need that column only done.

Okay so here we are with all of this. I did exactly the formula that you gave me this here in data validation
=AND(ISNUMBER(C2),LEN(C2)=10,COUNTIF(C2:C300,C2)=1)

it goes in with no errors YEY1!!! BUTTTT
i go and test it and it gives me errors.
by testing it i mean...i go to add a new contact (MYSELF) with forms and i input my phone number( and i tried different names too) that is not in the data and it says error data validation restriction..suggestions?

 

[vsaenz]

MISSISSIPPI PROSPECT LISTS.xlsx
	A	B	C	D	E	F	G	H
1	First Name	Last Name	Phone#	Work type	Called on	Called by	Notes	Column1
2	Thelma	Kirby	6015436428	csr	10/28/20	Vane	no msg left
3	Robert	Johns	5044133095	sales/csr	Set intv 10/29		set intv 10/29@2pm
4	Craig S	Forshee	2283130210	mngr	wrong #
5	Antoinette	Briscoe	2102516860	sales/csr			Lm
6	Arlene	McInnis	6625442286	sales/csr			Casino multiple 6 yrs
7	Fred M	Thomas	2285475536	mngr			no answer
8	Misty	Boekhoff	6019285503	mngr			wrong # call back
9	Teresa	Sanderson	2282816257	labor			not looking work hung up fast
10	Dillon C	Donnelly	2282237751	sales/csr			Set inv insulation and waterpark 12pm
11	Nicholas	Dyer	6623647226	sales/csr			full vm
12	Georgia	Blackmon	2283468868	sales/csr			Set intv@ 1230pm first name of the wrong number person Jessica----
13	Carrie	Yates	8126553034	sales/csr			looking for salaried Full time
14	Angela	Nations	2286711859	sales/csr	11/5	Jamie	set int 11/6 0930 mt time
15	Marekis	Hamptons	8033471990	sales/csr	11/6	Jamie	# not in service
16	Haylee	Logan	9045984474	sales/csr	11/6	Jamie	VM not set up
17	Shae	Blackwell	2515939675	sales/csr	11/6	Jamie	busy, FU monday	, only available after 5 pm
18	Allen J	Goudeau Jr	6017997305	labor	11/6	Jamie	VM not set up*** called back already has a job
19	Donald A.	Smith	7013703748	mngr	11/6	Jamie
20	Kenneth	Campbell	8508791689	sales/csr	11/6	Jamie	set int 11/6 1pm mt time
21	Jimmy	Caples	2514062303	mngr	11/6	Jamie	not intrsd
22	Angela	Fike	6622070706	sales/csr	11/6	Jamie	not intrsd
23	Brad	Quigley	9738167481	mngr	11/6	Jamie	set int 11/7 12 pm mt time
24	Hallie	Muzic	6063751648	sales/csr	11/6	Jamie
25	Isis	Stallworth	2282199492	sales/csr	11/6	Jamie	cannot complete as dialed
26	Tamara	Farrell	2283424132	sales/csr	11/16	Jamie	wrong #
27	Zadie	Lucas	5042914817	sales/csr	11/16	Jamie	int set 10:30 am central 11/17
28	David	Gean	2513488925	sales/csr	11/16	Jamie	not interested can call back later
29	Rebecca	Toutges	5025120118	sales/csr	11/16	Jamie	not in service
30	Corey	Melerine	9857106393	sales/csr	11/16	Jamie	LM
31	Alisa	Gilson	2288613931	sales/csr	12/9	Jamie	not interested can call back later
32	Martin	Fleming	3605513512	sales/csr	12/9	Jamie	not interested can
33	Khandija B	Jefferson	2516564212	medical	12/9	Jamie	LM
34	Hallie	Muzic	6023751648	mngr	12/9	Jamie	number incorrect
35	Shelia	Evans	5048105519	csr	12/9	Jamie	set int noon central thursday 12/20
36	Stephanie	Morgan	2512226670	mngr	12/19	Jamie	LM
37	J Scott	McElroy	2286232301	sales/csr	12/9	Jamie	set int friday 10 am central
38	Danielle	Rogers	2282399359	medical	12/9	Jamie	LM
39	William C	Wyly	2283633626	csr	12/9	Jamie	vm not set up
40	James	Lewis	9075702992	sales/csr	12/9	Jamie	set int thurday 12 central time
41	Candace	Ladner	2282574840	csr	12/9	Jamie	LM
42	April	Hightower	7043699688	csr	12/9	Jamie	vm not set up
43	Sherry	Miller	2288613309	LifeIns License	12/9	Jamie	try in a couple weeks
44	Vernon	White	6624044427	mngr	12/9	Jamie	not interested
45	Alison	Ignacio	2282133470	hr/csr	12/9	Jamie	LM
46	Irene	Morton	9855164383	csr	12/11	Jamie	LM
47	Christa	Shultz	2289903291	csr	12/11	Jamie	LM
48	Diane	Tennille	2282972082	csr	12/11	Jamie
49	Russell	Parks	2282242847	mngr
50	April	Vandevort	2283461745	csr
51	Quaneisha	Cooley	2289901483	csr
52	Tracy L	Briggs	2282540342	myb veteran
53	Jamie	Varnado	2286698778	myb veteran
54	Michael	Galloway	2283803683	veteran
55	Alexandra	Duffy	7246746928	veteran
56	Jacob	Miller	2283427972	veteran
57	Oliver	Johnson	2286799782	military
58	Dowl	Williams	2283140132	myb veteran
59	Floyd	Leak	7327201240	myb veteran
60	Scott	Drennan	2512349819	myb veteran
61	Leslie	refer from another caller	228-239-3224	referall	12/9 LM Jamie
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
C1:C61	Custom	=AND(ISNUMBER(C2),LEN(C2)=10,COUNTIF(C2:C300,C2)=1)

 

[DanteAmor]

I only want the whole column

Then use the formula from post #19

=AND(ISNUMBER(C1),LEN(C1)=10,COUNTIF(C:C,C1)=1)

(but I'm still lost, I don't know why you insist with this formula = AND(ISNUMBER(C2),LEN(C2)=10,COUNTIF(C2:C300,C2)=1))

__________________________________________________________________

I am already totally lost.

 

[DanteAmor]

You can't capture something like this: 228-239-3224
you must capture: 2282393224

And if you want the dashes to be seen, then you use the cell format. (see post #15)

Something to keep in mind when using data validation is, if you copy a cell on top of the cell with data validation, then the validation formula is lost.

 

[vsaenz]

You can't capture something like this: 228-239-3224
you must capture: 2282393224

And if you want the hypens to be seen, then you use the cell format.

Something to keep in mind when using data validation is, if you copy a cell on top of the cell with data validation, then the validation formula is lost.

okay thank you...got it!

 

[DanteAmor]

If you want a more robust validation, it could be with VBA. ?

 

[vsaenz]

If you want a more robust validation, it could be with VBA. ?

that's hilarious! I will have to pay someone to do that for me

 

[DanteAmor]

Ehhh, no, if you want I can provide you with the code and you can try it.