Data validation list - Automatically update cells with the same option

[Karl1]

Hi,

I'm trying to create a monthly timetable spreadsheet that allows you to select the session name for a particular timeslot from a drop down list (to avoid typo's). I have set up the format and the source list, which allows you to select a session, however if you go back to the source list and change any detail on that session, the change appears in the drop down, but does not update any of the previous cells where the same session was selected. What I really need is for the drop down selections and the source list to remain linked in some way, or for the change to get reflected back to the others.

The set up is:

Source list sheet called "SessionDetails", with list (range) of sessions (e.g. Sociology2, TM) within that worksheet called "Session_Detail". The drop down list points to this list.

The output sheet is called "TimetableDetail" and has a grid of timetable slots by day and hour in a range called "Timetable". You select a session detail per slot from the dropdown.

Occasionally we may alter and entry in the Session_Detail list to change the initial of the teacher. When this is done on the source list, I really need this to be reflected back on the Timetable ouput sheet. We have upto 500 entries so this really does need to be automatic.

I'm a little new to VBA and am now pulling my hair out, so any help would be greatly appreciated.

Many thanks in advance,

Karl

 

[Damon Ostrander]

Hi Karl,

Here is some code that will update all your previous selections to the new values when the List Validation table is edited. Here's the code:

Dim LastItem      As String

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   If Sh.Name <> "SessionDetails" Then Exit Sub
   If Intersect(Target, Worksheets("SessionDetails").Range("Session_Detail")) Is Nothing 
   Dim WS      As Worksheet
   Dim Cell    As Range
   Application.EnableEvents = False
   For Each WS In Worksheets
      For Each Cell In WS.UsedRange
         If HasListValidation(Cell) Then
            If Cell.Text = LastItem Then
               Cell.Value = Target.Value
            End If
         End If
      Next Cell
   Next WS
   Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   If Sh.Name <> "SessionDetails" Then Exit Sub
   If Intersect(Target, Worksheets("SessionDetails").Range("Session_Detail")) Is Nothing Then Exit Sub
   'save newly select element of List2
   LastItem = Target.Text
End Sub

Function HasListValidation(R As Range) As Boolean
   'returns TRUE if range or cell contains list validation
   HasListValidation = False
   On Error Resume Next
   HasListValidation = (R.Validation.Type = xlValidateList)
End Function

Important: ALL of this code should be pasted into the ThisWorkbook code module.

Note that SessionDetails, the name of the worksheet and SessionDetail, the name of the Validation List range are hard-wired into this code in several places. You will need to change these if the sheet or range name changes.

Keep Excelling.

Damon

 

[dereidar]

Could this be done without a macro?

 

[jbeaucaire]

No. Drop down lists put a flat value into a cell, not a formula. The only way to solve this scenario where you want one flat value replaced with a new flat value is with VBA.

There are several solutions I've seen over the years, some simpler, some more complex like the one above, but they're all VBA.

 

[jbeaucaire]

This is a technique I've used, whenever you use a drop down on your sheet, this macro instantly changes the selection you made into a formula that has the same result. If you ever go and change the value of the choice in the original list, all the cells that currently resolve to that same value "position" will update automatically since they're all formulas.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strValidationList As String
Dim strVal As String
Dim lngNum As Long

On Error GoTo Nevermind
strValidationList = Mid(Target.Validation.Formula1, 2)
strVal = Target.Value
lngNum = Application.WorksheetFunction.Match(strVal, Range(strValidationList), 0)

If strVal <> "" And lngNum > 0 Then
    Application.EnableEvents = False
    Target.Formula = "=INDEX(" & strValidationList & ", " & lngNum & ")"
End If

Nevermind:
    Application.EnableEvents = True
    
End Sub

 

[dereidar]

The 1st formula worked with regular numbers, but I'm having problems with multicells be used to form the values for the Validation cell...


A1: 12:30 - 1:30 =TEXT(B1,"H:mmA/P")&" - "&TEXT(C1,"H:mmA/P")
B1: 12:30 < formated as time
C1: 1:30 < formated as time

A1:A10 are named as list_schedule on sheet "Data"

The validation list points to it as =list_schedule, on sheet "Schedule"

I updated the code to reflect the sheet name of Data, but will not update the cells... Is it because of the formula I have in cell A1 to combine B1/C1?

Dim LastItem      As String
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   If Sh.Name <> "Data" Then Exit Sub
   If Intersect(Target, Worksheets("Data").Range("list_schedule")) Is Nothing Then Exit Sub
   Dim WS      As Worksheet
   Dim Cell    As Range
   Application.EnableEvents = False
   For Each WS In Worksheets
      For Each Cell In WS.UsedRange
         If HasListValidation(Cell) Then
            If Cell.Text = LastItem Then
               Cell.Value = Target.Value
            End If
         End If
      Next Cell
   Next WS
   Application.EnableEvents = True
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   If Sh.Name <> "Data" Then Exit Sub
   If Intersect(Target, Worksheets("Data").Range("list_schedule")) Is Nothing Then Exit Sub
   'save newly select element of List2
 
   LastItem = Target.Text
End Sub
Function HasListValidation(R As Range) As Boolean
   'returns TRUE if range or cell contains list validation
   HasListValidation = False
      On Error Resume Next
   HasListValidation = (R.Validation.Type = xlValidateList)
End Function

 

[jbeaucaire]

Apologies, you're using the other codes...

 

[dereidar]

jb, would your code work with the problem that i am having? I used that one because it came up 1st.

 

[dereidar]

JB, You're code worked from another site worked for me. The problem I am having is I used =Index(Myrange,2) will only list the one of the avaliable options from my validation list


i.e.
A1:A26 = A to Z, but only the A is displaying...

 

[dereidar]

Nevermind, thank you JB... Works mint