Multiple Dependant Dropdowns

[mattahmadi62]

Hi everyone,

I'm trying to create a workbook for my job and create multiple drop downs using data validation, but using a single source (another drop down as my trigger for my dependant lists). Here's my data:

[TABLE="width: 308"]
<TBODY>[TR]
[TD]Investor</SPAN>
[/TD]
[TD]Claim</SPAN>
[/TD]
[TD]Fees due</SPAN>
[/TD]
[TD]Amortization</SPAN>
[/TD]
[/TR]
[TR]
[TD]Aurora</SPAN>
[/TD]
[TD]Good</SPAN>
[/TD]
[TD]Good</SPAN>
[/TD]
[TD]Good </SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]None</SPAN>
[/TD]
[TD]None</SPAN>
[/TD]
[TD]None</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20</SPAN>
[/TD]
[TD]5</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[/TR]
[TR]
[TD]Bana</SPAN>
[/TD]
[TD]Good </SPAN>
[/TD]
[TD]Good</SPAN>
[/TD]
[TD]N/A</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]None</SPAN>
[/TD]
[TD]None</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bank Atlantic</SPAN>
[/TD]
[TD]Good</SPAN>
[/TD]
[TD]Good </SPAN>
[/TD]
[TD]Good </SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]None</SPAN>
[/TD]
[TD]None</SPAN>
[/TD]
[TD]None</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

My first drop down is the investor, the options are either Aurora, Bana, and Bank Atlantic (easy data validation that I know how to execute), but here's the challenging part [for me]: There are 3 different categories (Claim, Fees Due, & Amortization), when I choose Aurora, I want the claim cell to provide the 3 options (Good, None, or 20); however, if I choose Bana, I want the claim cell to provide Good, None, or 10. Lastly if Bana is choosen, then the only option for Amortization category should be solely N/A. Thoughts?

 

[rob.barnes01]

This could probably be accomplished by creating named ranges for your Claim, Fees Due & Amortization dropdowns and populating these ranges using IF statements tied to the value in column A ...

 

[teylyn]

Hello,

if you fill in the blank cells and sort the table by the first column, then you could do something like this:

Excel 2010

	A	B	C	D	E	F	G	H	I
Investor	Claim	Fees due	Amortization	Investor	Select investor
Aurora	N/A	Good	Good	Aurora	select Claim
Aurora	None	None	Bana	Select Fees due
Aurora	Bank Atlantic	Select Amortization
Bana	Good	Good	N/A
Bana	None	None
Bana
Bank Atlantic	Good	N/A	Good
Bank Atlantic	None	None
Bank Atlantic

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="bgcolor: #FFFF00, align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="bgcolor: #FFFF00, align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="bgcolor: #FFFF00, align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="bgcolor: #FFFF00, align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

Then set up these named formulas and use them in the data validation.
[TABLE="width: 167"]
<tbody>[TR]
[TD]Investor[/TD]
[TD]=Sheet1!$F$2:$F$4[/TD]
[/TR]
[TR]
[TD]Claim[/TD]
[TD]=IF(INDEX(Sheet1!$B:$B,MATCH(Sheet1!$I$1,Sheet1!$A:$A,0))="N/A",INDEX(Sheet1!$B:$B,MATCH(Sheet1!$I$1,Sheet1!$A:$A,0)),INDEX(Sheet1!$B:$B,MATCH(Sheet1!$I$1,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(Sheet1!$I$1,Sheet1!$A:$A,1)))[/TD]
[/TR]
[TR]
[TD]FeesDue[/TD]
[TD]=IF(INDEX(Sheet1!$C:$C,MATCH(Sheet1!$I$1,Sheet1!$A:$A,0))="N/A",INDEX(Sheet1!$C:$C,MATCH(Sheet1!$I$1,Sheet1!$A:$A,0)),INDEX(Sheet1!$C:$C,MATCH(Sheet1!$I$1,Sheet1!$A:$A,0)):INDEX(Sheet1!$C:$C,MATCH(Sheet1!$I$1,Sheet1!$A:$A,1)))[/TD]
[/TR]
[TR]
[TD]Amortization[/TD]
[TD]=IF(INDEX(Sheet1!$D:$D,MATCH(Sheet1!$I$1,Sheet1!$A:$A,0))="N/A",INDEX(Sheet1!$D:$D,MATCH(Sheet1!$I$1,Sheet1!$A:$A,0)),INDEX(Sheet1!$D:$D,MATCH(Sheet1!$I$1,Sheet1!$A:$A,0)):INDEX(Sheet1!$D:$D,MATCH(Sheet1!$I$1,Sheet1!$A:$A,1)))[/TD]
[/TR]
</tbody>[/TABLE]

If the first entry for an investor in any of the columns is "N/A", then the named formula and hence the data validation will only show that one entry.

cheers,

 

[mattahmadi62]

I tried entering this information in, but I didn't get any option for dropdowns (aside from the investor one). I tried it a couple different ways, but I don't think it's working

 

[BenMiller]

See - http://www.contextures.com/xlDataVal02.html

 

[mattahmadi62]

I looked at that website, but still couldn't figure out the proper way to create what I'm looking for.

See - http://www.contextures.com/xlDataVal02.html

 

[teylyn]

Take another look at my suggestion above.

You need to use the range names in a data validated cell, of course, otherwise there won't be a drop down.

So, for the data layout as above, set up cell I2 data validation with a list and the source is =Claim
Cell I3 needs data validation with list and the source is =FeesDue
Cell I4 needs data validation with list and the source is =Amortization

The range name definitions are depending on the Investor being selected in cell I1, so if you have that selection in a different cell, you need to adjust the range names accordingly. Best way might be to copy the range name definition formulas into a worksheet and do a find/replace, then copy the changed formulas back into the name manager

 

[BrianExcel]

I agree with Ben Miller - the "indirect" function is the best to use for Dependent Drop Downs in Excel.

 

[teylyn]

I'd like to see this being done with less effort with Indirect(). You'd need many more range names than just the three and you'd still need to cater for offering only N/A in the drop-down if the first value is N/A.

The suggestion I offer above is even dynamic. You don't need to change a thing if more values are added to each Investor.

The Contextures article covers a much simpler scenario than this one, and Indirect() is not necessarily the "best" function for dependent dropdowns.

So far, I have posted the only comment with a concrete solution. The rest were just hazy hints.

 

[BrianExcel]

Wow - yeah teylyn, you really are the best. The rest of us are just trying to catch up in this world where you are king because of your hyper-awesome Excel skills.

How about this - people post different solutions and the user chooses what works best for them?

Novel idea? I think so.