CODEWITHSUNDEEP
excelexcel-formula
Charlene Barina
Charlene Barina

Reputation: 197

named range using an if statement with array formula for dependent dropdown

Stumped after looking for a bit...

I have a spreadsheet with items like so:

   A              B                  C
+------+----------------------+--------------+
| Code |         Desc         |     Type     | 1
+------+----------------------+--------------+
|    1 | Main item            | Activity     | 2
|  1.1 | Sub item             | Sub-activity | 3
|  1.2 | Another sub item     | Sub-activity | 4
|    2 | Another main item    | Activity     | 5
|  2.1 | Yet another sub item | Sub-activity | 6
+------+----------------------+--------------+

I want to create a dropdown based on Activity. I can do this in a typical cell (with ctrl + shift + enter for array formula):

={if(c2:c6="Activity",a2:a6,"")}

But I can't figure out how to put that formula into a named range properly. When I hit ctrl + shift + enter, no braces appear. When it's without braces, it doesn't seem to work, either (it shows the value as {...}).

Is there a way to make this work?

Thanks in advance

Upvotes: 2

Views: 1499

Answers (2)

Charlene Barina
Charlene Barina

Reputation: 197

In the end, this wasn't possible via a named range; I ended up doing a variant of dependent dropdowns with offsets as well as two pivots, based on the blog page from Darren's comment above as well as this link. May be overkill, but at least I know how I got to it.

Setting up the first dropdown data source and data validation for the dropdown

  1. For the first list, I created a pivot from the dropdown data source with row of column "Type" and values of count "Type" (the values aren't that relevant, but I found it useful to just know how any elements to expect for later dependent items). This pivot is in the standard default pivot location on a new sheet, where the header row starts on A3. Using a pivot also sorts it by default alphabetically (which I wanted). Turn of all total columns.

  2. I then created a named range ("costCategory") with the following formula:

    =OFFSET('PivotSheet'!$A$4,0,0,COUNTA('PivotSheet'!$A$4:$A$100),1)

    That basically makes a list of the items and removes any blanks. It's not as dynamic as I'd like, but I think it's very unlikely I'll ever get beyond ~100 items on the list so I decided to live with it.

  3. I created another named range ("emptyList") with the following formula:

    ={""}

    So that I could also lock the first dropdown if the second, dependent one is selected (to prevent weird non-matching data issues).

  4. For the table rows that needed the dropdowns, I put in the data validation for a list with this formula:

    =IF(ISBLANK($B3),costCategory,emptyList)

    where $B3 is the second, dependent dropdown location.

Setting up the second, dependent dropdown data source and data validation 5. I created another pivot from the same data source, with rows of "Type" and "Desc", and values of count of "Type" (again, the values not a big deal). The pivot layout was set to tabular, repeating labels, no totals or subtotals. I put this pivot next to the other one, with the first header row starting at E3. It also alpha sorts.

  1. I put in helper columns to determine where the list starts for a particular parent of the dependent dropdown, and the number of rows of that list. It uses the same arbitrary long ranges approach as in the first dropdown - just put in a number of rows unlikely to be exceeded in the pivot. In col C, for getting the first row where the dependent data starts, I put this formula:

    =ROW(INDEX('PivotSheet'!$F$4:$F$200,MATCH($A3,'PivotSheet'!$E$4:$E$200,0)))

    In col D, for getting the number of cols where there is dependent data, I put this formula:

    =(LOOKUP(2,1/('PivotSheet'!$E$4:$E$200=$A3),ROW('PivotSheet'!$F$4:$F$200))-ROW(INDEX('PivotSheet'!$F$4:$F$200,MATCH($A3,'PivotSheet'!$E$4:$E$200,0))))+1

  2. Finally, in the column with the dependent dropdown (col B), I used the following data validation rule:

    =OFFSET('PivotSheet'!$F$1,$C3-1,0,$D3,1)

    Which basically takes the range that's found in the helper cols to make the dropdown list.

When these formulas are extended, they increment (A3 to A4, B3 to B4, etc.) so that they all still work even as you add rows to the listobject table.

Upvotes: 1

Imran Malek
Imran Malek

Reputation: 1719

Create a helper column in column D and write simple formula

=IF(C2="Activity",A2,"")

Once you have column D , you can create a list from column D ignoring the blank cells which is what you want.

P.S. If you're not concerned about the blank cells use the formula in column E2 and drag below which will give you consecutive values that you want to see in your list.

=IFERROR(SMALL($D$2:$D$6,ROW()-1),"")

Upvotes: 0

Related Questions