Using named range for conditional data validation

Question

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?

Marcel · Accepted Answer · 2014-12-04T14:38:43.353

1

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.

edited Dec 04 '14 at 14:38

answered Dec 03 '14 at 22:07

Marcel

2,764
1
24
40

I had to download your example to check what was wrong in my code but it looks like you have the same problem. Why isn't name1 in the filtered names in your example? And why is name30 here since the sum in V is 0? Then I noticed that name24 and name25 are missing instead of name23 and name24. It does work if I start with range A1:A200 and V1:V200 instead of A2:A200 and V2:V200. – dan Dec 04 '14 at 13:28
Also, the list will show blank values at the end. I tried with NA() instead of an empty cell for the second parameter of IFERROR() but the #N/A's just show up in the list menu too. "Ignore blank cells" is already checked. So for the list, I just used `=INDIRECT("myList!$W$1:$W$" & myList!$X$2)` where `X2={MIN(IF($W$1:$W$200="",ROW($W$1:$W$200)))-1}`. – dan Dec 04 '14 at 13:42
@dnLL you are right. I updated the formula and also the download link, it works fine now! tell me if sth goes wrong. – Marcel Dec 04 '14 at 14:39
Well I kind of fixed it by starting at row one so that it includes the column title anyway (doesn't really mind since the sheet is hidden and I then can use rows 2 to 201 for my validation range). The use of `INDIRECT()` with another function looking for the first empty cell in the column seems to be needed in the validation range to avoid blank cells showing in the menu. So it's perfectly working right now. Thank you very much! – dan Dec 04 '14 at 14:44
using indirect will increasing the calculation process, you'd be better off without it. Try my new worksheet and see if it works for you. There is no empty blanks in the list and there is no other mistake whatsoever. – Marcel Dec 04 '14 at 14:45
1

You are using `=Sheet1!$X$2:$X$26` as the validation range (well, as your named range). How do you dynamically determine that your last name is on row 26? You hardcoded it lol, I can't do that since it will change every month through the year with new employees. That's why I'm using the `{MIN(IF($W$1:$W$200="",ROW($W$1:$W$200)))-1}` formula (which would return 26 in your example) and then INDIRECT(). It's just for the data validation so the INDIRECT is actually just executed once, I don't think it should make that much of a difference really. – dan Dec 04 '14 at 14:54

score 0 · Answer 2 · answered Dec 03 '14 at 20:49

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.

Since it can definitely be done with Excel formulas, I want to avoid VBA when possible. I tried with VLOOKUP() but it's not interpreted as a valid array for the data validation range. Maybe I could look into a dynamic named range instead but I have no idea how and where to setup my condition either way. — dan, Dec 03 '14 at 20:57

Using named range for conditional data validation

2 Answers2