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?

 

[Peter_SSs]

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

Thomas

I am a little confused by the description of your layout but my understanding is that you have a layout something like my Sheet1 below and you don't want to be able to pick the same name twice in a single column, but picking the same name in different columns is OK.
If that is what you want, I think any solution without using some vba would be very unwieldy, especially if you have a lot of columns.
If vba is acceptable, then you might have a look at mikerickson's code as a possible starting point and/or post back with more details, including what version of Excel you are working with.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		01-Jan-14	02-Jan-14	03-Jan-14	04-Jan-14	05-Jan-14	06-Jan-14	07-Jan-14	08-Jan-14	09-Jan-14
2	Client 1						Ted
3	Client 2		Col		Don	Bob	Don
4	Client 3						Col
Sheet1

 

[Thomas1234]

Peter,

Your lay-out is exactly what I meant. In your example it would not be OK tot put Col under Jan 2 for Client 3 as he is already dedicated to client 2 that same day. However, he would be able to work on Client 2 on Jan 3.

I've tried mikerickson's code ad it works fine, at least for the first column (let's say Jan 1 in your example). However, I do not know how to make it work for the other columns/days.

Any help would be much appreciated!

KR,

Hederik

 

[Peter_SSs]

See how this goes.

My Data Validation list items are in A2:A21 on Sheet3 (edit code below to match your sheet name)

1. B1 houses a 0
2. C2 is a stand-alone formula
3. Formula in D2 is copied down as far as the end of the column A list
4. J1 is empty
5. Give cell J1 the Name RemainingList

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

Now on Sheet1 where the Data Validation will be used ..

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		01-Jan-14	02-Jan-14	03-Jan-14	04-Jan-14	05-Jan-14	06-Jan-14	07-Jan-14	08-Jan-14	09-Jan-14
2	Client 1						Ted
3	Client 2		Col		Don	Bob	Don
4	Client 3						Col
Sheet1

#VALUE!

6. Select the range to have Data Validation applied (B2:J4 in my example)
7. Apply Data Validation with Allow: List , Source: =RemainingList , Remove the tick from 'Ignore Blank'
8. Right click the sheet name tab for this sheet and choose 'View Code'
9. Copy the code below and Paste into the main right hand pane that opened at step 8.
10. Close the vba window and try selecting/entering cells on Sheet1.

If you are using Excel 2007 or later, your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim Col As String
  Dim RefTo As Range

  Const DVListSheet As String = "Sheet3"  '<- Name of sheet containg the DV list items
  
  Const FrmlaBase As String = "=IF(COUNTIF('%'!#:#,A2),"""",LOOKUP(9.99E+307,B$1:B1)+1)"

  If Target.Count = 1 And Target.Row > 1 And Target.Column > 1 Then
    Col = Split(Target.Address(1, 0), "$")(0)
    With Sheets(DVListSheet)
      .Range("B2:B" & .Range("A" & .Rows.Count).End(xlUp).Row).Formula = _
        Replace(Replace(FrmlaBase, "#", Col, 1, -1, 1), "%", Target.Parent.Name, 1, -1, 1)
      Set RefTo = .Range("J1")
      On Error Resume Next
      Set RefTo = .Range("D2").Resize(.Range("C2").Value)
      On Error GoTo 0
    End With
    On Error Resume Next
    ActiveWorkbook.Names("RemainingList").Delete
    On Error GoTo 0
    ActiveWorkbook.Names.Add Name:="RemainingList", RefersTo:=RefTo
  End If
End Sub

 

[Thomas1234]

Peter,

Your solution works perfectly!

However, one thing that still bothers me is that when a user does not select from the drop down list but instead enters a value directly in a cell that is already entered in one of the cells above/below, he will not get a warning or a message of some kind.

If in your example I would assign "Ted" to B2 till E2 (=Client 1), I would still be able to assign him to row 50 (Client 49) for that same period (select period, type "Ted" and then ctrl+enter) without being notified that Ted is no longer available. Since I would assign people for longer periods of time to a same client, it would become quite labor intensive to select from the drop down list in every single cell of that chosen period.

Any suggestions or a bridge too far?

KR,

Thomas

 

[Peter_SSs]

See if this is closer to the mark.

Set up Sheet3 like this. (No formulas, No named range)

Excel Workbook
	A	B
1	Orig List	Remaining List
2	Ann
3	Bob
4	Col
5	Don
6	Ern
7	Fay
8	Gil
9	Hal
10	Ian
11	Jan
12	Ken
13	Len
14	Mel
15	Ned
16	Ono
17	Pat
18	Que
19	Ron
20	Sam
21	Ted
22
Sheet3

Use the following code in the Sheet1 Module

Option Explicit

Const DVRange As String = "B2:J4"       '<- Cells you want the DV to apply to

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim Col As String
  Dim RefTo As Range
  Dim lr As Long

  Const DVListSheet As String = "Sheet3"  '<- Name of sheet containg the DV list items
  
  Const FrmlaBase As String = "=IF(C$1=A2,A2,IF(COUNTIF('%'!#:#,A2),"""",A2))"

  If Not Intersect(Range(DVRange), ActiveCell) Is Nothing Then
    Col = Split(ActiveCell.Address(1, 0), "$")(0)
    With Sheets(DVListSheet)
      .Range("C1").Value = ActiveCell.Value
      With .Range("B2:B" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .Formula = _
          Replace(Replace(FrmlaBase, "#", Col, 1, -1, 1), "%", ActiveCell.Parent.Name, 1, -1, 1)
        .Value = .Value
        On Error Resume Next
        .SpecialCells(xlBlanks).Delete Shift:=xlUp
        On Error GoTo 0
      End With
      lr = .Range("B" & .Rows.Count).End(xlUp).Row
      If lr = 1 Then lr = 2
      Set RefTo = .Range("B2:B" & lr)
    End With
    On Error Resume Next
    ActiveWorkbook.Names("RemainingList").Delete
    On Error GoTo 0
    ActiveWorkbook.Names.Add Name:="RemainingList", RefersTo:=RefTo
    With ActiveCell.Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="=RemainingList"
      .IgnoreBlank = True
      .InCellDropdown = True
    End With
  End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim DVR As Range, Changed As Range, c As Range
  Dim msg As String

  Set DVR = Range(DVRange)
  Set Changed = Intersect(Target, DVR)
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      With c
        If WorksheetFunction.CountIf(Intersect(.EntireColumn, DVR), .Value) > 1 Then
          msg = msg & vbLf & .Address(0, 0) & vbTab & .Value
          .ClearContents
        End If
      End With
    Next c
    Application.EnableEvents = True
    If Len(msg) Then
      MsgBox "One or more invalid entries were made." & vbLf & _
              "The following cells/values have been cleared." & vbLf & msg
    End If
  End If
End Sub

 

[Thomas1234]

Peter,

Exactly what I was looking for! Thank you so much for your time and help!

Kind regards,

Thomas

 

[Peter_SSs]

Great, thanks for letting us know.

 

[darkanger4]

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.

Hello fellow Excellers ,
I have exactly the same problem like the original poster (mgirvin) and mikerickson VBA code works to some extent for me, however when i enter duplicate value in the column which the VBA code checks for duplicates and the popup shows "No duplicates allowed in A1:A15" and I click "OK" the duplicated value just stays in the cell. Is it possible when i press the OK button the VBA code to automatically clear the duplicated value from the cell?
Thank you in advance and have a great day all!

 

[darkanger4]

No one can help me ?
I am sad panda

 

[Peter_SSs]

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
                [COLOR="#FF0000"]Target.ClearContents[/COLOR]
                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.

Hello fellow Excellers ,
I have exactly the same problem like the original poster (mgirvin) and mikerickson VBA code works to some extent for me, however when i enter duplicate value in the column which the VBA code checks for duplicates and the popup shows "No duplicates allowed in A1:A15" and I click "OK" the duplicated value just stays in the cell. Is it possible when i press the OK button the VBA code to automatically clear the duplicated value from the cell?
Thank you in advance and have a great day all!

I don't understand what is happening in your circumstance. You say Mike's code works "to some extent", what does that mean?

The part of Mike's code that removes the duplicate value is highlighted red above. That occurs immediately before the message box, so I don't see how the message box could appear without the cell being cleared.

Perhaps you could give more detail about what you have and what you are trying to achieve as there must be something different about your setup.