Data Validation

[Lost Cluster]

I’m doing data validation on a group of cells. At the moment all the names
of the employees appear on the drop down list for those cells, but what I
need is to compare those names to a day-off list where the people who are off
on that day appear. If their names appear on the day-off list then they
should not appear on my data validation cells.

In other words I have colA-colG with the names of the week, Mon-Sun.
I have 30 rows. The first 15 rows are for my data validation and they are
drop downs of the people who are working on that day. The source for those
rows comes from a separate sheet where all the names of my employees are
listed. Rows 16-30 have the names of the people who are off that day. So
the names in Rows 1-15 should not match 16-30.

Thanks.

 

[Bob Ryan]

I’m doing data validation on a group of cells. At the moment all the names
of the employees appear on the drop down list for those cells, but what I
need is to compare those names to a day-off list where the people who are off
on that day appear. If their names appear on the day-off list then they
should not appear on my data validation cells.

In other words I have colA-colG with the names of the week, Mon-Sun.
I have 30 rows. The first 15 rows are for my data validation and they are
drop downs of the people who are working on that day. The source for those
rows comes from a separate sheet where all the names of my employees are
listed. Rows 16-30 have the names of the people who are off that day. So
the names in Rows 1-15 should not match 16-30.

Thanks.

If I understand you correctly, a name should only appear once in a
column. I would use COUNTIF formulas in Columns H thru N. For example,
if the formula =COUNTIF(A$2:A$31,A2) in Column H, Rows 2-31 shows a
number greater than 1, that says there is more than one occurrence of a
particular name. Or, to make it easier to spot, you combine an IF
function with a COUNTIF formula such as
=IF(COUNTIF(A$2:A$31,A2)>1,"PROBLEM",1) so that the word PROBLEM would
appear any time there's more than one occurrence of a name.
Hope this is helpful.
Bob Ryan

 

[Otto Moehrbach]

You would need VBA (programming) for that. But there is one decision for
you to make first. VBA would create a DV list for each day of the week
based, of course, on what is entered in rows 16-30. In practice, you would
enter one name in rows 16-30, then maybe another name, then maybe another
name, and so on. What you have to decide is when do you want VBA to setup
those DV lists? Every time a name is entered? Or maybe when you are
through entering names in rows 16-30? But how would VBA know you are
through? Or maybe place a button somewhere on the sheet that you would
click on to tell VBA to create those lists? Another question I would need
answered is do you want all 7 days calculated at one time? If not, an
option would be for you to click on the day-of-the-week cell in row 1 and
this would trigger VBA to calculate the DV list for that day only. Post
back with your thoughts. HTH Otto

 

[Otto Moehrbach]

Here are 2 macros that will do what you want. I assumed the sheet with
lists is named "Lists". Change this as needed. This code will fire when
you click on one of the header cells in row 1, in columns A to G. It will
set up the DV cells in that column.
The first macro is a sheet event macro and must be placed in the sheet
module of your sheet (not the Lists sheet). To access that module,
right-click the sheet tab and select View Code. Paste this macro into that
module. "X" out of the module to return to your sheet. The second macro
goes in a regular module. Post back if you need more. HTH Otto

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row = 1 And Target.Column < 8 Then
Call SetupList(Target.Column)
End If
End Sub

Sub SetupList(c As Long)
Dim Off As Range
Dim i As Range
Dim TheDVName As String
Dim Dest As Range
Set Off = Range(Cells(16, c), Cells(30, c))
Select Case c
Case 1: TheDVName = "Mon"
Case 2: TheDVName = "Tue"
Case 3: TheDVName = "Wed"
Case 4: TheDVName = "Thu"
Case 5: TheDVName = "Fri"
Case 6: TheDVName = "Sat"
Case 7: TheDVName = "Sun"
End Select
With Sheets("Lists")
If Not IsEmpty(.Cells(2, c).Value) Then
.Range(.Cells(2, c), .Cells(Rows.Count,
c).End(xlUp)).ClearContents
End If
Set Dest = .Cells(2, c)
If Application.CountA(Off) = 0 Then
Range("All").Name = TheDVName
Else
For Each i In Range("All")
If Off.Find(What:=i, Lookat:=xlWhole) Is Nothing Then
Dest = i.Value
Set Dest = Dest.Offset(1)
End If
Next i
.Range(.Cells(2, c), .Cells(Rows.Count, c).End(xlUp)).Name =
TheDVName
End If
End With
End Sub

 

[zvkmpw]

I’m doing data validation on a group of cells. At the moment all the names

of the employees appear on the drop down list for those cells, but what I
need is to compare those names to a day-off list where the people who areoff
on that day appear. If their names appear on the day-off list then they
should not appear on my data validation cells.
In other words I have colA-colG [for] Mon-Sun.
I have 30 rows. The first 15 rows are for my data validation and they are
drop downs of the people who are working on that day. The source for those
rows comes from a separate sheet where all the names of my employees are
listed. Rows 16-30 have the names of the people who are off that day. So
the names in Rows 1-15 should not match 16-30.

The following seems to work with Excel 2003.
For convenience, I put complete employee list in J1:J15 of the same
sheet. Putting them on a different sheet is a straightforward change.

I use rows 31:46 as helper rows.

Leave row 31 empty.

In A32 put
=IF(COUNTIF(A$16:A$31,$J1)>0,"",MAX(A$31:A31)+1)
Then copy A32 down to A46. Then select A32:A46 and copy rightward to
column G.

In A1 put
=IF(ROW()>MAX(A$32:A$46),"",
OFFSET($J$1,MATCH(ROW(),A$32:A$46)-1,0))
Then copy A1 down to A15. Then select A1:A15 and copy rightward to
column G.

Modify to suit.