User Forms with Dependent Drop-down Selections

Hi all,

I have two doubts I would like to share to the wider community for help. I am trying to enable multiple dependent drop-down forms with multiple selections (combining some VBA code) to collect some data. Nonetheless, I am facing two issues when doing so.

1. I have a drop-down in which you can select from 1 to 9 options. This should trigger a second drop-down based on the concatenated sub-categories made in those selections on the next column (e.g.: If you selected "fish" and "vegetables"; in the next column you should have all fishes and all vegetables inside the dropdown [mackerel, salmon, cucumber, spinach]). This, which can be intuitive, has been a nightmare on the backend:
   1. I had to go to a programming language (R in my case) to create all the different 511 possible combinations of items from the selections, putting all titles alphabetically ordered and in named_ranges style.
   2. I pasted that creation to Excel, the 511 different named ranges and automatically created a formula for crafting 511 named ranges taking into account the different lengths.
   3. Finally, I created on the main table a helper column ordering the first selection alphabetically (with yet again another VBA formula) so that I could match with =INDIRECT("helper_column_cell") the different possible concatentations. A long journey given the fact I did not see a way to seamlessly join different named ranges...
2. A second issue has to do with the multiple selection. I achieved having the dropdown with the right data depending on the first 1 to 9 combinations input; enabling me to select multiple items from the resulting list, concatenating them with commas and not allowing repetitions. Nonetheless, each input requires reopening the drop-down again, and I would like that to be done in one shot. I thought about a user form, but it looks as adapting the options to the 511 possible combinations is dreamland. Below you see the code I do have for now:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDataMapping As Range
    Dim Oldvalue As String
    Dim Newvalue As String
    Dim arr() As String
    Dim Output As String
    Dim i As Integer

    ' Set the range for the multiple drop-down (columns O and R in this case)
    Set rngDataMapping = Intersect(Me.Range("O:O, R:R, S:S"), Target)
    
    If Not rngDataMapping Is Nothing Then
        Application.EnableEvents = False
        On Error GoTo Exitsub
        If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub Else
        If Target.Value = "" Then GoTo Exitsub Else
        Application.Undo
        Oldvalue = Target.Value
        Application.Undo
        Newvalue = Target.Value
        
        ' Check if the new value already exists
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            If Oldvalue = "" Then
                Target.Value = Newvalue
            Else
                arr = Split(Oldvalue, ", ")
                Output = ""
                For i = LBound(arr) To UBound(arr)
                    If arr(i) <> Newvalue Then
                        If Output = "" Then
                            Output = arr(i)
                        Else
                            Output = Output & ", " & arr(i)
                        End If
                    End If
                Next i
                If Output = "" Then
                    Output = Newvalue
                Else
                    Output = Newvalue & ", " & Output
                End If
                Target.Value = Output
            End If
        End If
    End If

Exitsub:
    Application.EnableEvents = True
    Exit Sub
End Sub

Do you have any recommendation on better and more efficient solutions to Problem 1? Is there any way to sort out the issue on problem 2 to allow the users to do multiple selections without the drop-down collapsing with any new input? I would really appreciate your mastery and comments!