Multi Level Dependent Drop Down Lists

Hello All,

Got another a pretty straightforward yet tricky issue on my hands here.

Background:

We are developing a new chart of accounts. However, we need to put control over the users ability to input the account number itself. As such, it has been decided to create a drop down list for the user to select to which account to post.

The multi level dependent drop down list will have 7 levels. Once a user selects 1/16 options from the first "Main Code" section, the options available on the next level "Detail Code" should be restricted to only what is applicable to the first selection. Once the user goes through the first two levels, the options available on the third level should again be restricted and so on.

I have attached a sample spreadsheet. The interdependencies have been colored coded as best as possible to show the related options. Please note that for the last 3 levels "LOB Code", "Island Code", and "Department Code" all options should be available regardless of what has been selected in other lists. The items under "company/Project Code" that have two colors means that they can be allocated to either the red or the green section, i.e. "Bank and Finance Charges" & "Marketing" under the "main Code"

In Summation:

7 level dependent drop down list.

Each level has its own section for the user to select an item.

My approach thus far:

Create named ranges and use the indirect formula. But this is giving me a bit of difficulty. Just wondering if there is a better way or if someone could help give some insight.

Thanks!

I have not looked at your filer, but dependent DD's are relatively simple to put together. However, they become exponentially more involved, the more levels you have.

My version is based on having range names for each category, and then more range names for each sub-category...and then for each sub-sub category. INDIRECT is then used to reference the level-above-selection, to pick out "this" level's item

See the attached, is uses this principal for 3 levels

To remove attachments, Edit>Go Advanced>Manage Attachments and click on the "X" to remove the attachment. Then you can re-upload.

Are you sure you want 7 layers of lists? If each list only has 4 items, that totals 21,844 unique items. I think the only way to do this is with a lot of manual work setting up the individual lists.

In order to do that, you will need to use VBA, something like this in the worksheet module

Please Login or Register  to view this content.