CODEWITHSUNDEEP
excelvbaexcel-formuladynamic-listexcel-2021
AbsRa
AbsRa

Reputation: 23

Dynamic list which adds selected items into a single cell on new rows

I have students, teachers and teacher planning worksheets and need to find a neat and dynamic solution to the following in the 'Scheduling' table:

  1. lookup teacher names - this I can do!

  2. based on teacher name, dynamically bring up what they have previously planned in the activity column. Different teachers can teach the same topic taught by a previous teacher - only their planning notes might differ.

  3. based on the student attendance register for the day, a dynamic list of students need to come up, but crucially, a teacher can select one or more student names to be in their activity.

  4. at any given time, 3 activities take place. Based on Teacher 1 student selection, Teacher 2 cannot choose the same students selected by Teacher 1, and similaryl Teacher 3, can only select students who are present but not selected by the other teachers.

Hoping this all makes sense with the following screenshots:

enter image description here

enter image description here

enter image description here

I have tried a bunch of xlookups. My real problem is how to append student names to a single cell (in different lines) based on teacher selection.

I really don't want to use VBA as I just don't have the knowledge for this. I want to be able to achieve this primarily with excel formulas.

Upvotes: 0

Views: 83

Answers (1)

IvanSTV
IvanSTV

Reputation: 364

some time ago I've written a function then returns all the found values in one cell split by strings Arguments of function:

SearchArr - range we search value

Col - from what column we return

SrchVal - what value we search

Function code:

Function VlookupALL(ByRef SearchArr As Variant, ByVal Col As Integer, ByVal SrchVal As String)
Dim Result As String
For Each x In SearchArr
If CStr(x) = CStr(SrchVal) Then
    If Len(Result) = 0 Then
        Result = Cells(x.Row, Col)
    Else:
        Result = Result & vbCrLf & Cells(x.Row, Col)
    End If
End If

Next x
VlookupALL = Result
End Function

Upvotes: 0

Related Questions