vsaenz
New Member
 Joined
 Jul 27, 2021
 Messages
 21
 Office Version

 2016
 Platform

 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!
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!