CODEWITHSUNDEEP
validationexceldrop-down-menuexcel-2010vba
Makai
Makai

Reputation: 617

Parts of an unsorted list into a drop down

I am trying to create a data validation drop down cell that displays a list of values pulled from a much larger list, but only the ones where the lookup value meet certain requirements. This would be like the SUMIF function that only adds the values where the lookup value meet certain requirements. Here is an example of my list:

                V   F
Apples              x
Bananas             x
Tangerines          x
Tomatoes        x   x
Broccoli        x   
Pears               x
Kiwis               x
Plums               x
Water melon         x
Squash          x   x

I want only the ones with an "x" in the first column to display in the drop down. 

Tomatoes
Broccoli
Squash

Also the original list can't be sorted. I am fine with using macros if that would work. I am using Excel 2010.

Upvotes: 0

Views: 596

Answers (3)

pnuts
pnuts

Reputation: 59475

If you want a range of valid entries without blanks to use as a list for data validation, I suggest something like: 

=INDEX($A$2:$A$11,SMALL(IF($B$2:$B$11<>"",ROW($A$2:$A$11)-ROW($A$2)+1),ROWS(C$2:C2)))

entered with Ctrl+Shift+Enter

There is about 20 minutes of explanation at https://www.youtube.com/watch?v=6PcF04bTSOM.

SO19100440 example

Upvotes: 1

Aaron Thomas
Aaron Thomas

Reputation: 5281

Using VBA, you could use this as a starter. The key is the Range.Validation method, which is explained in detail here. This reads your list in column A, finding those with an "x" in column B, and puts that in a validation list in cell E1.

Dim myvalidation_list As String
Dim last_row As Long, current_row As Long

last_row = Cells(Rows.Count, "A").End(xlUp).Row

For current_row = 1 To last_row
  If LCase(ActiveSheet.Cells(current_row, 2).Value) = "x" Then
    'put in the delimiting "," if the list already has an entry
    If myvalidation_list <> "" Then
      myvalidation_list = myvalidation_list & ","
    End If
    'add to the validation list
    myvalidation_list = myvalidation_list _
    & ActiveSheet.Cells(current_row, 1).Value
  End If
Next

With ActiveSheet.Range("E1").Validation
  .Delete
  .Add Type:=xlValidateList, Formula1:=myvalidation_list
End With

Upvotes: 0

Aaron Thomas
Aaron Thomas

Reputation: 5281

Without using VBA, you could create a copy of the list that is filtered. You can then reference the cells in that copy when you use data validation.

For example, you could do the following steps for your example above:
Apply a filter to the list where only those showing an x in the first column are showing. Copy the filtered list, then paste to another spot on the worksheet. Turn off the filter on the list, so it returns to normal. Go to the cell that you want to add a validation drop down to, and select data validation. Select list, then reference the copied list.

Upvotes: 0

Related Questions