Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-17-2023, 08:23 PM
firozmohammed firozmohammed is offline Data Validation Windows 11 Data Validation Office 2021
Novice
Data Validation
 
Join Date: Nov 2023
Posts: 3
firozmohammed is on a distinguished road
Smile Data Validation

I have prevented duplicate entries in a column containing numbers using data validation. when trying to enter a number which does not exist in the said column, error message displays and entry is prevented. Please help


.
Reply With Quote
  #2  
Old 11-17-2023, 11:08 PM
Guessed's Avatar
Guessed Guessed is offline Data Validation Windows 10 Data Validation Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,891
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Either remove the data validation or show us the formula you used to do that data validation.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 11-17-2023, 11:16 PM
firozmohammed firozmohammed is offline Data Validation Windows 11 Data Validation Office 2021
Novice
Data Validation
 
Join Date: Nov 2023
Posts: 3
firozmohammed is on a distinguished road
Default

Thank you for the prompt response and help. I have used the following formula:
=COUNTIF($E$3:$E$20000,E3)=1

Thank you once again.
Reply With Quote
  #4  
Old 11-17-2023, 11:27 PM
Guessed's Avatar
Guessed Guessed is offline Data Validation Windows 10 Data Validation Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,891
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

This reference's method works for me
Excel formula: Data validation unique values only

Allow = Custom
Make sure you are ignoring blanks
Try <2 instead of =1
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #5  
Old 11-18-2023, 01:55 AM
firozmohammed firozmohammed is offline Data Validation Windows 11 Data Validation Office 2021
Novice
Data Validation
 
Join Date: Nov 2023
Posts: 3
firozmohammed is on a distinguished road
Default

That worked. Thank you very much for the help
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Validation Data validation the other way round? Officer_Bierschnitt Excel 3 11-13-2015 10:12 PM
Data Validation data validation from another sheet paulys Excel 1 06-30-2014 01:40 AM
Data Validation Data Validation drop down rkalapura Excel 1 05-27-2012 02:23 AM
If/then formulas with data validation shira47 Excel 1 04-09-2012 05:14 AM
Data validation with IF Klyxrastafari Excel 1 10-03-2011 04:41 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:51 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft