Dynamic lists from multiple sources

[Pennycook]

Any help graciously accepted here!.

I am aiming to create a resource forecasting schedule. I have 2 datasets to compare and condition.

List 1 is a list of individual tasks that have different people assigned as competent to carry out these tasks and thus are available for selection.

List 2
An absence list containing the names of those who are on leave in the upcoming date range.

Ideally, I'd like to have a dropdown list in a separate third table allowing only those in table 1 who are available for the unique tasks and not on holiday in list 2 on the certain day.

So for task 1 on 21/10/2019 (highlighted green) the resulting dropdown would provide only options for Matthew and John because Cain, Able, Joseph and Mary are on holiday. Not sure how the blanks factor into this or how to eliminate those that are in both datasets either.

I'm a relative novice here so I have no idea if this can be done but it would be of great benefit if possible. Thanks for reading.

 

[Akuini]

Hi, Pennycook
Welcome to the Forum.

1. Are you ok to use vba?
2. Can you post the example above as table not image? so we can easily copy it to a worksheet. In your worksheet, just select the table, copy then paste it directly into the reply box.
3. What are the table names? All in the same sheet?

 

[Pennycook]

Thanks so much. Any help appreciated.

Matthew	Matthew			Matthew		Matthew		Matthew
	Mark			Mark		Mark	Mark
	Luke	Luke	Luke		Luke	Luke	Luke
John		John	John		John	John	John	John
Cain	Cain	Cain	Cain	Cain	Cain	Cain	Cain	Cain
Able	Able		Able	Able	Able		Able	Able
Joseph	Joseph		Joseph	Joseph	Joseph		Joseph
Mary	Mary	Mary		Mary	Mary	Mary	Mary

21/10/2019	22/10/2019	23/10/2019	24/10/2019	25/10/2019	26/10/2019	27/10/2019	28/10/2019	29/10/2019	30/10/2019
	Matthew	Matthew	Matthew	Matthew	Matthew		Matthew
	Mark	Mark	Mark	Mark	Mark		Mark
		Luke	Luke	Luke	Luke		Luke		Luke
			John	John	John				John
Cain	Cain				Cain				Cain
Able	Able				Able		Able		Able
Joseph	Joseph	Joseph	Joseph		Joseph		Joseph		Joseph
Mary	Mary	Mary	Mary		Mary		Mary		Mary

Task ID	21/10/2019	22/10/2019	23/10/2019	24/10/2019	25/10/2019	26/10/2019	27/10/2019	28/10/2019	29/10/2019	30/10/2019
Task 1
Task 2
Task 3
Task 4
Task 5
Task 6
Task 7
Task 8
Task 9

 

[Pennycook]

Happy to use VBA where needed.

Competent, Absence and Schedule are the table names in order of the above

Thanks

 

[Akuini]

Using vba, how to set up:
1. Create a named range "zName1" refer to: =Sheet1!$Z$1:INDEX(Sheet1!$Z:$Z, COUNTA(Sheet1!$Z:$Z), 1)
You may change the sheet name to suit.
2. In "Schedule" create the data validation > List > Source: =zName1
3. Copy paste the code to the sheet code module (this is an Event Procedure so you need to put it in the code module of the sheet).

Note:
1. I use col Z as helper column, you may change it by amending the named range formula above.
2. I change some part of your example (highlighted yellow) to show what happen if no data is found.
3. The list is sorted ascending.

The code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo SKIP:

If Target.Cells.CountLarge <> 1 Then Exit Sub

    With ActiveSheet.ListObjects("Schedule").DataBodyRange
        If Not Intersect(Target, .Cells(1, 2).Resize(.Rows.Count, .Columns.Count - 1)) Is Nothing Then
            Dim tbl1 As ListObject, tbl2 As ListObject
            Dim i As Long, n As Long, xc As Long
            Dim c As Range
            Dim a As String, b As String
            Dim va, vb, g, h, x
            Dim d As Object

           Set tbl1 = ActiveSheet.ListObjects("Competent")
           Set tbl2 = ActiveSheet.ListObjects("Absence")
          
           xc = Range("zName1").Column
           a = Cells(Target.Row, .Columns(1).Column)
           b = Cells(.Offset(-1).Row, Target.Column)
           g = Application.Match(a, tbl1.HeaderRowRange, 0)
           h = Application.Match(b, tbl2.HeaderRowRange, 0)
          
           Application.EnableEvents = False
           Application.ScreenUpdating = False
           Range("zName1").ClearContents
           Cells(1, xc) = "-- NO DATA --"
          
           If IsNumeric(g) And IsNumeric(h) Then
                va = tbl1.DataBodyRange.Columns(g)
                vb = tbl2.DataBodyRange.Columns(h)
                Set d = CreateObject("scripting.dictionary")
                d.CompareMode = vbTextCompare
                    
                    For i = 1 To UBound(va, 1)
                       d(va(i, 1)) = Empty
                    Next
                    
                    For Each x In vb
                        If d.Exists(x) Then d.Remove x
                    Next
                    
                    If d.Exists("") Then d.Remove ""
                    
                    If d.Count > 0 Then
                        Set c = Cells(1, xc).Resize(d.Count)
                        c = Application.Transpose(Array(d.keys))
                        c.Sort Key1:=c.Cells(1), Order1:=xlAscending, Header:=xlNo
                    End If
                
           End If
           Application.EnableEvents = True
           Application.ScreenUpdating = True
        End If
    End With
Exit Sub
SKIP:
MsgBox "Error number " & Err.Number & " : " & Err.Description
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

The workbook:

dhee - a1116922a - dynamic-lists-from-multiple-sources.1116922 - 1.xlsm

Shared with Dropbox

www.dropbox.com