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?

 

[teylyn]

Brian, correct me if I'm wrong: What concrete solution has been offered here except the working one that I posted? What exactly has been offered as an alternative apart from links to faintly similar (but not quite appropriate) scenarios and your "I agree"? I posted a suggestion that actually works. If the forum would offer the facility to upload a workbook, I'd happily do so. Because I only post a suggestion after I've tested it.

You didn't even post anything approaching a solution. You just stated that Indirect is the best approach for dependent data validation. That's "tosh", to quote my friend Bob.

Have you tested your approach? Have you even developed it? Does it work? If so, how? Where are the details?

As far as I can see, my suggestion is the only working solution according to the specs of the OP.

Why don't you post exactly how to solve the problem with Indirect(), then? With concrete formulas instead of "I agree". I challenge you. Can you deliver?

I'm up for a challenge. Beat my suggestion with Indirect, if you feel that it's the "best" to use for dependent drop downs, with the special requirements of the question. Read the OP again. I've shown how to solve the question with my suggestion. And it works. Show me your working solution before you criticise my approach.

Or are you all hot air?

cheers,

 

[mattahmadi62]

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

Hi again,

I wanna thank you for all your time as I feel i'm really close to getting this right, but I'm still having an issue with the provided formula. I entered everything as is, and it works (sorta), I have a drop down for the investor section, but only get a drop down option for "N/A" and not the "Good,None,10" for any of the selected investor.

 

[BenMiller]

Brian, correct me if I'm wrong: What concrete solution has been offered here except the working one that I posted? What exactly has been offered as an alternative apart from links to faintly similar (but not quite appropriate) scenarios and your "I agree"? I posted a suggestion that actually works. If the forum would offer the facility to upload a workbook, I'd happily do so. Because I only post a suggestion after I've tested it.

You didn't even post anything approaching a solution. You just stated that Indirect is the best approach for dependent data validation. That's "tosh", to quote my friend Bob.

Have you tested your approach? Have you even developed it? Does it work? If so, how? Where are the details?

As far as I can see, my suggestion is the only working solution according to the specs of the OP.

Why don't you post exactly how to solve the problem with Indirect(), then? With concrete formulas instead of "I agree". I challenge you. Can you deliver?

I'm up for a challenge. Beat my suggestion with Indirect, if you feel that it's the "best" to use for dependent drop downs, with the special requirements of the question. Read the OP again. I've shown how to solve the question with my suggestion. And it works. Show me your working solution before you criticise my approach.

Or are you all hot air?

cheers,

http://bit.ly/L1aCEd

 

[BrianExcel]

Awesome Ben. Love it. I'm sure Excel Guru will be chiming in any moment with his comments.

Wait for it......

 

[teylyn]

Hi mattahmadi62,

I've uploaded a working example here: 641034-Multiple-Dependant-Dropdowns.xlsx

Please let me know if this works for you.

cheers,