If you want to limit the choices available for the values that can be entered into a cell, you could use a data validation list.
In the example image below, we are creating a roster for the teachers that are on playground duty during the week.
The data is laid out as a table, and I am using conditional formatting to highlight any duplicates across rows, and down columns. No teacher can be on duty twice in the same day, or be on duty for the same period during the week.
So when I select Claire for A.M. Recess & Lunch on Thursday, my conditional formatting rules warn me. Likewise when I assign Bob to A.M. Recess & Lunch on Friday, and Lunch on Wednesday, the CF rules let me know.
This works nicely but we have to set up data validation for each cell in the table. If we write a little VBA, we can use just one DV list to populate the entire row.
In the image below we can see I am using just one list at the start of each row, and each time I make a selection, the next empty cell in the row is populated.
Not only that, if I make a mistake, I can choose the blank first entry in the list, and this deletes the last value in the row. Or I can press the Delete key to do the same thing.
I can also delete cells in the middle of the row (and they don't have to be adjacent cells), then use the list to fill in the gaps.
This table is using the same conditional formatting rules as the first table.
The VBA uses the Worksheet_Change event so you'll find the code in the Sheet1 module.
Right click this link and download the .xlsm workbook
Option Explicit ' http://www.myonlinetraininghub.com/selecting-multiple-items-in-data-validation-list ' Written by Philip Treacy ' Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Count > 1 Or (Target.ListObject.HeaderRowRange(Target.Column - Target.ListObject.HeaderRowRange.Column + 1) <> "Monday") Or (Not Intersect(Target, Target.ListObject.HeaderRowRange) Is Nothing) Then On Error GoTo 0 Exit Sub End If Dim PreviousValue As String Dim NewValue As String Dim LastTableCol As Long Application.EnableEvents = False Application.ScreenUpdating = False On Error GoTo 0 LastTableCol = Target.ListObject.HeaderRowRange.Column + (Target.ListObject.HeaderRowRange.Columns.Count - Target.ListObject.HeaderRowRange.Column + 1) NewValue = Target.Value Application.Undo PreviousValue = Target.Value If PreviousValue = "" Then Target.Value = NewValue Else If NewValue = "" Then Target.Offset(, -1).End(xlToRight).Value = NewValue Else ' I would normally use .ListObject.Name to test if cell was in the table ' but this wasn't working correctly at this point. A Bug? ' so I'm checking that the column is inside the table On Error Resume Next If Target.Offset(, -1).End(xlToRight).Offset(0, 1).Column <= LastTableCol Then Target.Offset(, -1).End(xlToRight).Offset(0, 1).Value = NewValue End If On Error GoTo 0 End If End If Application.EnableEvents = True Application.ScreenUpdating = True End Sub
Sharing is Caring
Think this is the best thing since Slicers? Then please click the buttons below to share it with your friends and colleagues.