Excel VBA Drop Down List with dependent list with multiple selection

Question

I have a spreadsheet where clients have to make Drop Down selections for (1) Account, (2) Account Name and (3) Account Categories throughout excel and are dependent of each other. I actually did a non-VBA Drop Down List dependent of each other. The problem is that the drop down list only works one at a time instead of making multiple selections.

So what I want of course is the drop down list to work for not just one row, but selections of many rows. The only way this Drop Down List would work non-VBA is by creating as my many Unique List depending on the number of accounts and their dependents.

So far without using Excel VBA I have done the following:

I have Defined Name the 3 Main List ranges: GLacct,GLname, & GLcat:
Created - 3 Unique List: So this Unique List is populated when the first DDL "55700" is selected, next DDL will populate "Research - Other" and then the next DDL will populate 3 choices correspondent to "Research -Other".

So to create the Unique List in excel I added these formulas:

UniqueGLacct: `INDEX(GLacct,MATCH(0,COUNTIF($F$1:F1,GLacct),0))`

UniqueGLname: 
    `INDEX(GLname,MATCH(0,COUNTIF($G$1:G1,GLname)+(GLacct<>TM!$A$16),0))`

UniqueGLcat: 
    `INDEX(GLcat,MATCH(0,COUNTIF($H$1:H1,GLcat)+(GLacct<>TM!$A$16)+(GLname<>TM!$B$16),0))`

Name Manager – Refers to and added the following formulas:

GLacct: Offset(TB!$A$2,0,0,COUNTA(TB!$A$2:$A$1000)) GLname: Offset(TB!$B$2,0,0,COUNTA(TB!$B$2:$B$1000)) GLcat: Offset(TB!$C$2,0,0,COUNTA(TB!$C$2:$C$1000))

uniqueGLacct: OFFSET(TB!$F$2,0,0,COUNT(IF(TB!$F$2:$F$1000=””,””,1)),1) uniqueGLname: OFFSET(TB!$G$2,0,0,COUNT(IF(TB!$G$2:$G$1000=””,””,1)),1) uniqueGLcat: OFFSET(TB!$H$2,0,0,COUNT(IF(TB!$H$2:$H$1000=””,””,1)),1)
Added the Data Validation to cells that need the drop down list in Sheet named ™: ( The problem is if I where to make another selection in the next row, I still get the data from the first selection, which means in order to work I have to make as many Unique List for each account.

cell: A16: uniqueGLacct

cell: B16: uniqueGLname

cell: C16: uniqueGLcat

How would I make this Excel Drop Down List dynamic for 3 List dependent for multiple selections using Excel VBA. But if you notice accounts below 55700 can't popular their dependent list because the first selection made is 55700. The only way this would work without using VBA is by creating Unique List for each account dependent.

enter image description here

Kelvin Kelvin · Accepted Answer · 2015-02-25T21:47:04

I'm not sure you can't do this without a macro.

1) First you could change your reference style to R1C1: Link to great explanation

2) Then you would create a named range for each unique list (I am assuming they are finite). enter image description here

3) Then you would add validation List and then Indirect to an name of that range (in the header or an offset cell) to each indirect.

With this, you can just say "This Row, Column offset" or whatever is applicable: enter image description here In this case I selected the same row, one column to the left which is the name of the named range that I wanted in the drop down.

enter image description here

The benefit of this is that you have 1 formula across all of your cells. You can change the display back to A1 type display and the formulas will adapt.

Excel VBA Drop Down List with dependent list with multiple selection

1 Answers