Data validation with 3 Criteria's already have data form

vsaenz

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Dec 17, 2010
Messages
81
Office Version
  1. 2019
Platform
  1. Windows
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))
 
Last edited:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,753
Office Version
  1. 2007
Platform
  1. Windows
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?
Excel Formula:
[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

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
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.
__________________________________________________________________

Excel Formula:
[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

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
MISSISSIPPI PROSPECT LISTS.xlsx
ABCDEFGH
1First NameLast NamePhone#Work typeCalled onCalled byNotesColumn1
2ThelmaKirby6015436428csr10/28/20Vaneno msg left
3RobertJohns5044133095sales/csrSet intv 10/29set intv 10/29@2pm
4Craig SForshee2283130210mngrwrong #
5AntoinetteBriscoe2102516860sales/csrLm
6ArleneMcInnis6625442286sales/csrCasino multiple 6 yrs
7Fred MThomas2285475536mngrno answer
8MistyBoekhoff6019285503mngrwrong # call back
9TeresaSanderson2282816257labornot looking work hung up fast
10Dillon CDonnelly2282237751sales/csrSet inv insulation and waterpark 12pm
11Nicholas Dyer6623647226sales/csrfull vm
12GeorgiaBlackmon2283468868sales/csrSet intv@ 1230pm first name of the wrong number person Jessica----
13CarrieYates8126553034sales/csrlooking for salaried Full time
14AngelaNations2286711859sales/csr11/5Jamieset int 11/6 0930 mt time
15MarekisHamptons8033471990sales/csr11/6Jamie# not in service
16HayleeLogan9045984474sales/csr11/6JamieVM not set up
17ShaeBlackwell2515939675sales/csr11/6Jamiebusy, FU monday, only available after 5 pm
18Allen JGoudeau Jr6017997305labor11/6JamieVM not set up*** called back already has a job
19Donald A. Smith7013703748mngr11/6Jamie
20KennethCampbell8508791689sales/csr11/6Jamieset int 11/6 1pm mt time
21JimmyCaples2514062303mngr11/6Jamienot intrsd
22AngelaFike6622070706sales/csr11/6Jamienot intrsd
23BradQuigley9738167481mngr11/6Jamieset int 11/7 12 pm mt time
24HallieMuzic6063751648sales/csr11/6Jamie
25IsisStallworth2282199492sales/csr11/6Jamiecannot complete as dialed
26TamaraFarrell2283424132sales/csr11/16Jamiewrong #
27ZadieLucas5042914817sales/csr11/16Jamieint set 10:30 am central 11/17
28David Gean2513488925sales/csr11/16Jamienot interested can call back later
29RebeccaToutges5025120118sales/csr11/16Jamienot in service
30CoreyMelerine9857106393sales/csr11/16JamieLM
31AlisaGilson2288613931sales/csr12/9Jamienot interested can call back later
32MartinFleming3605513512sales/csr12/9Jamienot interested can
33Khandija BJefferson2516564212medical12/9JamieLM
34HallieMuzic6023751648mngr12/9Jamienumber incorrect
35SheliaEvans5048105519csr12/9Jamieset int noon central thursday 12/20
36StephanieMorgan2512226670mngr12/19JamieLM
37J ScottMcElroy2286232301sales/csr12/9Jamieset int friday 10 am central
38DanielleRogers2282399359medical12/9JamieLM
39William CWyly2283633626csr12/9Jamievm not set up
40JamesLewis9075702992sales/csr12/9Jamieset int thurday 12 central time
41CandaceLadner2282574840csr12/9JamieLM
42AprilHightower7043699688csr12/9Jamievm not set up
43SherryMiller2288613309LifeIns License12/9Jamietry in a couple weeks
44VernonWhite6624044427mngr12/9Jamienot interested
45AlisonIgnacio2282133470hr/csr12/9JamieLM
46IreneMorton9855164383csr12/11JamieLM
47ChristaShultz2289903291csr12/11JamieLM
48DianeTennille2282972082csr12/11Jamie
49RussellParks2282242847mngr
50AprilVandevort2283461745csr
51QuaneishaCooley2289901483csr
52Tracy LBriggs2282540342myb veteran
53JamieVarnado2286698778myb veteran
54Michael Galloway2283803683veteran
55AlexandraDuffy7246746928veteran
56JacobMiller2283427972veteran
57OliverJohnson2286799782military
58Dowl Williams 2283140132myb veteran
59Floyd Leak7327201240myb veteran
60Scott Drennan2512349819myb veteran
61Leslierefer from another caller228-239-3224referall12/9 LM Jamie
Sheet1
Cells with Data Validation
CellAllowCriteria
C1:C61Custom=AND(ISNUMBER(C2),LEN(C2)=10,COUNTIF(C2:C300,C2)=1)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,753
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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.
 
Solution

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,753
Office Version
  1. 2007
Platform
  1. Windows
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.
 
Last edited:

vsaenz

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2018
Messages
13,753
Office Version
  1. 2007
Platform
  1. Windows
If you want a more robust validation, it could be with VBA. 😅
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,753
Office Version
  1. 2007
Platform
  1. Windows
Ehhh, no, if you want I can provide you with the code and you can try it.
 

Forum statistics

Threads
1,143,687
Messages
5,720,301
Members
422,275
Latest member
Maria95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top