Log In
Register
Home
Countif in data validation - Prevent duplicate entries
lea cohen
Member
Members
Forum Posts: 248
Member Since:
March 10, 2016
March 10, 2016
Offline1
May 6, 2022 - 4:41 pm
Hi,
The attachment has 2 issues -
The DATA sheet has a column with numbers
In Sheet1 issue in column A - I enter data
I need Excel not to allow a number that exists in a DATA sheet to be typed
I set the following formula in data validation -
= COUNTIF (DATA! A: A, A2) = 0
And it does not work, why?
See Answer
Anders Sehlstedt
Eskilstuna, Sweden
VIP
Members
Trusted Members
Trusted Members
Forum Posts: 925
Member Since:
December 7, 2016
December 7, 2016
Offline2
May 7, 2022 - 5:57 pm
Hello Lea,
Your data validaton rule in cell A1 is =COUNTIF(DATA!A:A,A2)=0, which means that as long as cell A2 is empty or have allowed vaule (one that does not exist in your DATA sheet) you can type in whatever you want in A1.
Simply change the rule for A1 to =COUNTIF(DATA!A:A,A1)=0 and it will work as you intended.
Br,
Anders
Answers Post
The following users say thank you to Anders Sehlstedt for this useful post:
Philip Treacy
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online:
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
1 Guest(s)
Top Posters:
Catalin Bombea: 1939
SunnyKow: 1432
Anders Sehlstedt: 925
Purfleet: 415
Frans Visser: 349
Hans Hallebeek: 345
David_Ng: 312
lea cohen: 248
Jessica Stewart: 219
A.Maurizio: 216
Newest Members:
Karen Brown
Arieta Salihu
Garry Miller
Shuonan Zhou
Kelly Woolum
Belinda White
Barbara White
Ross McCarty
Lisa Donaldson
Hiroyuki Murai
Forum Stats:
Groups: 3
Forums: 25
Topics: 7088
Posts: 31068
Member Stats:
Guest Posters: 49
Members: 34510
Moderators: 2
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Jessica
Moderators: Velouria, Riny van Eekelen
© Simple:Press —