mgirvin
Well-known Member
- Joined
- Dec 15, 2005
- Messages
- 1,247
- Office Version
- 365
- Platform
- Windows
Dear Smartest Excelers In The World,
Is there a way to have Data Validation List and Data Validation Custom formula in the same formula?
Here is the complete problem description:
1) I made a list of 20 first-names of people on Sheet3, one first-name per cell, no formulas or anything on Sheet3, it's all typed letter by letter.
2) I named those cells on Sheet3 NAMES so that I can use Data Validation on it afterwards
3) I made 15 data validation cells on sheet1, each one is set to allow: LIST and the source for each one is NAMES
4) I'm happy because in each of those data validation cells on Sheet1 I have a dropdown list allowing me to choose amongst any of the firstnames on sheet3
5) I'm unhappy because I can choose one firstname in one data validation cell in sheet1 AND THE SAME ONE in another data validation cell in sheet1
(And this is for a timetable so I don't want 2 firstnames to be able to have the same timetable)
6) I found a method for disallowing duplicates amongst all my data validation cells: (hypothesis: let's say my 15 Data Validation cells on sheet1 are in A1:A15)
I select A1 then I select A1:A15, I go to DATA VALIDATION, I allow CUSTOM, and in SOURCE I put: =COUNTIF($A$1:$A$15,A1)=1
7) Now I cannot put the SAME firstname, in 2 different cells amongst my15 data validating cells in sheet1 BUT I lost my drop down list!!!
8) I want both at the SAME TIME in each of my 15 data validation cells on sheet1!!! (A dropdown data validation list AND disallow duplicates amongst those 15 data validation cells on sheet1)
Any ideas?
Is there a way to have Data Validation List and Data Validation Custom formula in the same formula?
Here is the complete problem description:
1) I made a list of 20 first-names of people on Sheet3, one first-name per cell, no formulas or anything on Sheet3, it's all typed letter by letter.
2) I named those cells on Sheet3 NAMES so that I can use Data Validation on it afterwards
3) I made 15 data validation cells on sheet1, each one is set to allow: LIST and the source for each one is NAMES
4) I'm happy because in each of those data validation cells on Sheet1 I have a dropdown list allowing me to choose amongst any of the firstnames on sheet3
5) I'm unhappy because I can choose one firstname in one data validation cell in sheet1 AND THE SAME ONE in another data validation cell in sheet1
(And this is for a timetable so I don't want 2 firstnames to be able to have the same timetable)
6) I found a method for disallowing duplicates amongst all my data validation cells: (hypothesis: let's say my 15 Data Validation cells on sheet1 are in A1:A15)
I select A1 then I select A1:A15, I go to DATA VALIDATION, I allow CUSTOM, and in SOURCE I put: =COUNTIF($A$1:$A$15,A1)=1
7) Now I cannot put the SAME firstname, in 2 different cells amongst my15 data validating cells in sheet1 BUT I lost my drop down list!!!
8) I want both at the SAME TIME in each of my 15 data validation cells on sheet1!!! (A dropdown data validation list AND disallow duplicates amongst those 15 data validation cells on sheet1)
Any ideas?