Data Validation List with dynamic list depending on related cell content

[mhoukes]

Maybe a hard to explain title, an example:

A list of products, with a value per year:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Product
[/TD]
[TD]2018
[/TD]
[TD]2019
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Apple
[/TD]
[TD]10
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Pear
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Melon
[/TD]
[TD]12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Orange
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Lemon
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Coconut
[/TD]
[TD]11
[/TD]
[TD]12
[/TD]
[/TR]
</tbody>[/TABLE]


I'm searching for a solution to have a Data Validation List (Or filtered name range) of products with a non empty value in 2019 (so in the example: Pear Orange and Coconut) as selectable items in a data validation drop down list (in another cell).
List must be dynamic, when add a value under 2019 for Apple, Apple must be part of the list as well.

Seems not a difficult question but can't find any related question or example which i can use to compose my own formula or procedure.

Thanks for helping me out.

Marcel Houkes

 

[mhoukes]

A small add-on, I think the easiest thing to accomplish this is to create a dynamic conditional name range.
Name range of products where related 2019 cell <> "". When done so, then I reach the same goal, how....

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in a blank cell in column C and exit the cell. The drop down list will be created in cell D1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range, join As String
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rngArray As Variant
    For Each rng In Range("C2:C" & LastRow)
        If rng <> "" Then
            join = join & rng.Text & ", "
        End If
    Next
    With Range("D1").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=Left(join, Len(join) - 2)
    End With
    Application.ScreenUpdating = True
End Sub

 

[mhoukes]

Not the solution i'm searching for, but thanks anyway for the quick response.