Data Validation or maybe another method?

wfsteadman

New Member
Joined
Dec 28, 2016
Messages
15
Greetings all,
I am trying to make a dropdown list for that will be used in cell D# where the # will be the row from say 60 - 90. Meaning I want the dropdown list to be in each of the D cells in rows 60-90. The issue i am trying to solve is how to create the list dynamically based on a couple of criteria

The values for the list will come from cells
B29-33, B37:41, B45:49, B53:57

And at times some of the cells may be blank so I don't want them to show up. Also there could be multiple cells with the same value but I would only like the value to be in the list once.

For example:
B29: port33
B30: port34
B31: port34
B32: port33
B33: BLANK

B37: port31
B38: BLANK
B39: port31
B40: port32

etc...
so in the list I would like it to only have the following options:

port31
port32
port33
port34

This list will be updated based on a worksheet change event.

Any assistance would be appreciated.

Thanks in Advance
Wally
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Can you make a "helper" list that will contain all codes without empty cells, and use that as the DV list?
 
Upvote 0
Hi
Give this a try. The output range is assumed to be in column C. Change to suit your needs.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B29:B33,B37:B41,B45:B49,B53:B57")) Is Nothing Then
    Call ExtractList
    Call SortData
End If


End Sub


Sub ExtractList()
Dim cell As Range
Dim rng As Range
Dim r As Long
Set rng = Range("B29:B33,B37:B41,B45:B49,B53:B57")


'Output range is in Column C
Range("C:C").ClearContents
r = 1
For Each cell In rng
    If cell <> "" Then
        If Application.CountIf(Range("C:C"), cell) = 0 Then
            Cells(r, 3) = cell.Value
            r = r + 1
        End If
    End If
Next


End Sub
Sub SortData()
    ActiveSheet.Range("C:C").Sort Key1:=Range("C1"), _
    Order1:=xlAscending, Header:=xlNo
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top