CODEWITHSUNDEEP
excelvalidationexcel-formulanamed-ranges
dan
dan

Reputation: 3519

Using named range for conditional data validation

I have a named range called "myList" defined as rows 1 to 200 of Sheet1. There are 20 columns, the 8 first columns are informations about the employee while the last 12 columns are 0s or 1s for inclusion of the employee in each month.

Now, I want to create a drop-down menu that will show me the name of the employees if there is at least a 1 in columns 9-20 of my named range.

For now, I use the following formula as data validation: =INDEX(myList,,1) and it shows me every name from myLast whether there is a 1 or not in the columns 9-20.

How could I change this so that the only names showing in the data validation creating a drop-down menu are the names on column 1 of myList where the corresponding SUM() of columns 9-20 of myList >= 1?

Upvotes: 0

Views: 1213

Answers (2)

Marcel
Marcel

Reputation: 2794

Here is a workabout to your question :
Create a column SUM after the last column and calculate the sum of each row. lets say column V of your datasheet
then use the following array formula to filter the names whose sum is >=1

lets say you put this formula in X1 and drag and fill down until the last row.

=IFERROR(INDEX($A$2:$V$31;SMALL(IF($V$2:$V$31>=1;ROW($V$2:$V$31));ROW(1:1))-1;1);"")


this is an array formula, so press ctrl+shift+enter to calculate the formula



then in Z1 create your dropdown list based on the filtered names in column X.
please replace the 31 with 201 because you will have 200 rows plus one row of headings, if it is the case. I used 30 items in my example sheet.
tell me if it works.
***UPDATED***
sorry there was a mistake in the formula, it is now corrected. Here is the corrected formula. It now works fine. I also changed the download link.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96791

You can compose a DV list without directly referring to a set of worksheet cells:

Sub NotUsingCells()
    Dim StringOfNames As String
    StringOfNames = "Larry,Moe,Curley"
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=StringOfNames
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

In your case, you would process myList row-by-row, building StringOfNames by concatenating the names that meet your criteria.

Upvotes: 0

Related Questions