Data Validation List and Custom formula in 1 cell

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,247
Office Version
  1. 365
Platform
  1. 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?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
Code:
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
 
Upvote 0
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
 
Upvote 0
move the parenthesis
Rich (BB code):
Not (Application.Intersect(Target, Range("A1:A15")) Is Nothing)
 
Upvote 0
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?
 
Upvote 0
With the correction above and the proper spelling of Events, this worked for me.
Code:
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.
 
Upvote 0
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

Excel Workbook
ABCD
1Orig List0No. AvailableNew List
2Ann 17Bob
3Bob1Don
4ColErn
5Don2Fay
6Ern3Gil
7Fay4Hal
8Gil5Ian
9Hal6Ken
10Ian7Len
11JanMel
12Ken8Ned
13Len9Ono
14Mel10Pat
15Ned11Que
16Ono12Ron
17Pat13Sam
18Que14Ted
19Ron15
20Sam16
21Ted17
Sheet3




On Sheet1
5. Make your data validation as shown.

Excel Workbook
AB
1Col
2Jan
3Ann
4
5
6
Sheet1
#VALUE!
 
Upvote 0
Dear mikerickson,

That worked!!!! Totally EXCELlent! Thank you so much. I am always amazed at all the Excellent Excelers like you that help out!
 
Upvote 0
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!!!
 
Upvote 0
Dear Peter,

I've a very similar Excel problem as mgirvin. However, I was wondering whether it would be possible to have the validation list as you described in several columns instead of one? If, in your example, I were to select Jon in row A1, I would not be able to select Jon again in row B1.

Reason why I am asking is because I am looking for a solution where Jon could be "hired" for a few days (assuming columns would equal days of the year) by client x (row 1), which implies that Jon would be unavaible to client y during those same days (row 2). In the current example your solution only works for 1 day.

Many thanks in advance for your help!

KR,

Thomas
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

*ABCD
Orig ListNew List
Ann*Bob
Bob*Don
Col**Ern
Don*Fay
Ern*Gil
Fay*Hal
Gil*Ian
Hal*Ken
Ian*Len
Jan**Mel
Ken*Ned
Len*Ono
Mel*Pat
Ned*Que
Ono*Ron
Pat*Sam
Que*Ted
Ron**
Sam**
Ted**

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 91px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="align: right"]0[/TD]
[TD="align: center"]No. Available[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="align: center"]17[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]

[TD="align: right"]5[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]

[TD="align: right"]6[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]

[TD="align: right"]7[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]

[TD="align: right"]8[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]

[TD="align: right"]9[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]

[TD="align: right"]10[/TD]

[TD="bgcolor: #CACACA, align: center"]15[/TD]

[TD="align: right"]11[/TD]

[TD="bgcolor: #CACACA, align: center"]16[/TD]

[TD="align: right"]12[/TD]

[TD="bgcolor: #CACACA, align: center"]17[/TD]

[TD="align: right"]13[/TD]

[TD="bgcolor: #CACACA, align: center"]18[/TD]

[TD="align: right"]14[/TD]

[TD="bgcolor: #CACACA, align: center"]19[/TD]

[TD="align: right"]15[/TD]

[TD="bgcolor: #CACACA, align: center"]20[/TD]

[TD="align: right"]16[/TD]

[TD="bgcolor: #CACACA, align: center"]21[/TD]

[TD="align: right"]17[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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)))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4



On Sheet1
5. Make your data validation as shown.

Sheet1

*AB
Col*
Jan*
Ann*
**
**
**

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

</tbody>

Data Validation in Spreadsheet
CellAllowDatasInput 1Input 2
A1List*=RemainingNames*

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0

Forum statistics

Threads
1,224,889
Messages
6,181,611
Members
453,057
Latest member
LE102024

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top