Log In
Register
Home
Countif in data validation - Prevent duplicate entries
lea cohen
Member
Members
Forum Posts: 237
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: 880
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: Sameh Alami, Donnacha Holly, Ineke Smit
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
Hans Hallebeek: 188
Newest Members:
JUDY MLL
Scot Bailey
Kate Dyka
Kwaje Alfred Mogga
thong nguyen
Appiagyei Kofi Frimpong
Hilary Burchfield
Richie Wright
Adel Kock
Barbara Murray
Forum Stats:
Groups: 3
Forums: 24
Topics: 6548
Posts: 28672
Member Stats:
Guest Posters: 49
Members: 32834
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: Velouria, Riny van Eekelen
© Simple:Press —