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!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

richh

Board Regular
Joined
Jun 24, 2007
Messages
243
Office Version
  1. 365
  2. 2016
I think you may need to wrap your code in an IF statement.

=IF(AND(COUNTIF($C$2:$C$63,C2)=1, LEN(C2)<10), [True], [False])
 

vsaenz

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
I think you may need to wrap your code in an IF statement.

=IF(AND(COUNTIF($C$2:$C$63,C2)=1, LEN(C2)<10), [True], [False])
okay i am going to try that now and let you know how it works. I appreciate the feedback.
Do you know if this works...will the formula continue to be applied to each new row added?
 

vsaenz

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
I think you may need to wrap your code in an IF statement.

=IF(AND(COUNTIF($C$2:$C$63,C2)=1, LEN(C2)<10), [True], [False])
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?
 

richh

Board Regular
Joined
Jun 24, 2007
Messages
243
Office Version
  1. 365
  2. 2016

ADVERTISEMENT

You have to change the [True]/[False] in the code to your own expectations. I don't know what you want displayed if your conditions are or are not met.
 

vsaenz

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
You have to change the [True]/[False] in the code to your own expectations. I don't know what you want displayed if your conditions are or are not met.
oooh okay awesome...that would help...let me try that
 

vsaenz

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

ADVERTISEMENT

OKAY so it went throught but now all the cells in that column have an error on them 🤦‍♀️ 🤦‍♀️
any suggestions?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,633
Office Version
  1. 2007
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Check the examples in the following link:

 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,633
Office Version
  1. 2007
Platform
  1. Windows
In this part try the following formula:

Data Validation
  • Data >> Data Tools >> Data Validation
  • Setting >> Custom
  • Formula:
Excel Formula:
=AND(ISNUMBER(C2),LEN(C2)=10,COUNTIF(C1:C2000,C2)=1)
 

vsaenz

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Check the examples in the following link:

thank you for the welcome! my issue is that im having to enter 3 different criteria in the data validation custom formula versus just one at a time. OR even two i would be okay with at this point. No duplicates and only phone numbers im about to give up on adding the dashes lol if you have any other resources for me to look into...im tired of watching countless youtube videos hahah
 

Forum statistics

Threads
1,141,817
Messages
5,708,760
Members
421,588
Latest member
Wawie

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