Data Validation List and Custom formula in 1 cell

[mgirvin]

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?

[mikerickson]

Something like this should work
Validate Sheet1!A1 with this and copy the validation to the other cells of Sheet1!A1:A15
=AND( ISNUMBER(MATCH(A1,NAMES,0)), (COUNTIF($A$1:$A$15,A1)=1)) will validate things.

But you don't have a List to choose from.

you could leave the validation on list and put this in the code module for

Sheet1Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And Not (Application.Intersect(Target, Range("A1:A15") Is Nothing)) Then
    If 1 < Application.CountIf(Range("A1:A15"), Target.Value) Then
        Application.EnableEvents = False
            Target.ClearContents
            MsgBox "No duplicates allowed in A1:A15"
        Application.enableevnets = True
    End If
End If
End Sub

[mgirvin]

Dear mikerickson,

Thank you so much for answering so quickly!

I just tried the code and got a "Compile error: type mismatch" error and the "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" part was a yellow color and the "Is Nothing" part was highlighted.

Any ideas?

This is the code that I pasted in sheet 1:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 And Not (Application.Intersect(Target, Range("A1:A15") Is Nothing)) Then
If 1 < Application.CountIf(Range("A1:A15"), Target.Value) Then
Application.EnableEvents = False
Target.ClearContents
MsgBox "No duplicates allowed in A1:A15"
Application.enableevnets = True
End If
End If
End Sub

[mikerickson]

move the parenthesis

Not (Application.Intersect(Target, Range("A1:A15")) Is Nothing)

[mgirvin]

Dear Mike,

EXCELlent! That got rid of the Yellow and the error message, but now when I add a duplicate record in A1:A15 on sheet1 it does not prevent it.

I have the code pasted in the sheet1 (sheet1) area. Is that correct?

[mikerickson]

With the correction above and the proper spelling of Events, this worked for me.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count = 1 And Not (Application.Intersect(Target, Range("A1:A15")) Is Nothing) Then
        If 1 < Application.CountIf(Range("A1:A15"), Target.Value) Then
            Application.EnableEvents = False
                Target.ClearContents
                MsgBox "No duplicates allowed in A1:A15"
            Application.EnableEvents = True
        End If
    End If
End Sub

Are events enabled or did it crash with them disabled.

The code module of sheet1 is where that should go.

[Peter_SSs]

mgirvin

Here is another method (non vba) that I think will do what you want.

Set up Sheet3 with some extra info (see below):
1. Your 'Names' range would be A2:A21 in my setup (though this named range will not be used in my solution)

2. B1 houses a 0, C2 the formula shown.

3. B2 and D2 formulas copied down to row 21.

4. Insert|Name|Define... In the 'Names in workbook:' box type RemainingNames and in the 'Refers to:' box type =OFFSET(Sheet3!$D$2,,,Sheet3!$C$2) and click OK

Sheet3

	A	B	C	D
1	Orig List	0	No. Available	New List
2	Ann		17	Bob
3	Bob	1		Don
4	Col			Ern
5	Don	2		Fay
6	Ern	3		Gil
7	Fay	4		Hal
8	Gil	5		Ian
9	Hal	6		Ken
10	Ian	7		Len
11	Jan			Mel
12	Ken	8		Ned
13	Len	9		Ono
14	Mel	10		Pat
15	Ned	11		Que
16	Ono	12		Ron
17	Pat	13		Sam
18	Que	14		Ted
19	Ron	15
20	Sam	16
21	Ted	17

Spreadsheet Formulas

Cell Formula B2 =IF(ISNA(MATCH(A2,Sheet1!$A$1:$A$15,0)),LOOKUP(9.99999999999999E+307,B$1:B1)+1,"") C2 =MAX(B2:B21) D2 =IF(ROWS(D$2:D2)>$C$2,"",INDEX($A$2:$A$21,MATCH(ROWS(D$2:D2),$B$2:$B$21,0)))

Excel tables to the web >> Excel Jeanie HTML 4



On Sheet1
5. Make your data validation as shown.

Sheet1

	A	B
1	Col
2	Jan
3	Ann
4
5
6

Data Validation in Spreadsheet

Cell Allow Datas Input 1 Input 2 A1 List   =RemainingNames

Excel tables to the web >> Excel Jeanie HTML 4

[mgirvin]

Dear mikerickson,

That worked!!!! Totally EXCELlent! Thank you so much. I am always amazed at all the Excellent Excelers like you that help out!

[mgirvin]

Dear SSs,

That is so amazing!! It's like Magic, what you formulas do! Thank you for the concept of a formula that finds one-more-than-is-in-the-list and also for the formula that creates a shrinking list!

You and Mike and many other MrExcel Excel Excelers are awesome!!!