Dependant list with illegal characters and multiple words

[bravobravoau]

Hi guys, I have had a look for answers to this problem and keep getting stuck. I will try to simplify the scenario.

Part of a work book I'm working on requires related columns - division name and section name of work areas responsible for certain functions. I need lists for both and am trying to use a dependant list for section (e.g. only the corresponding sections associated with the selected division are available). While I need to generate a dependant list, the division first range has cells with illegal characters (&) and multiple words. I'm not sure which is causing me problems to be honest.

Range A7:A10 is "Divisions". A2 has data validation allowing a list, with the source "=Divisions". This step works fine and returns all four divisions listed in the range.

Range A13:A16 is "Finance", A19:A21 is "HumanResources", A27:A30 is "FacilitiesANDSecurity" and A37:A39 is "Operations". I did this because the actual titles (for want of a better word), using multiple words and ampersands, are invalid.

B2 has data validation also allowing a list, with the source "=Indirect(A2)". Selecting either of the one-word division names in A2 (Finance and Operations) result in the related section names being visible in the list in B2. Selecting either of the other two division names results in no names in the list. THIS IS THE PROBLEM!!! How do I use the dependanty list so that division names with illegal characters or multiple words show the associated section names???!!

In the sample, below, the response is entered in A2:A4 and B2:B4 (3 entries for this purpose).

A B
1 Division Section
2
3
4
5
6 Divisions
7 Finance
8 Human Resources
9 Facilities & Security
10 Operations
11
12 Finance
13 Accounts
14 Reporting
15 Strategic Advice
16 Banking
17
18 Human Resources
19 Employee Entitlements
20 Conduct
21 Analytics
22
23 Facilities & Security
24 Facilities Management
25 Help Desk
26 Personnel Security
27 Protective Security
28
29 Operations
30 Team 1
31 Team 2
32 Team 6

I have tried to use a lookup table without succeeding in anything but further confusion. Hopefully there is a simple solution for someone far more competant with Excel than me. If you have a solution, please use simple language.

Many thanks

[bravobravoau]

I've attached a sample file - hopefully that helps my description of the problem??!!

[newdoverman]

Perhaps this will help you. All the lists have been combined into one main list and from there the various lists that are required are extracted.

[bravobravoau]

Thanks newdoverman - that solution appears to work. I'm not exactly clear how you've done it though, I assume with dynamic lists?

Can you give a simple step by step of your solution?

[newdoverman]

You are correct that the lists are dynamic. The whole "scheme" depends upon 3 names in the Name Manager.

1. MasterList =Lists!$A$2:INDEX(Lists!$1:$8,8,COUNTA(Lists!$1:$1))

This is a dynamic list that starts in worksheet Lists in A2 which is followed by : which is the second part of the required array to define MasterList. the INDEX part $1:$8 defines the top row Divisions, Finance etc , the next argument is the number of rows (8). The COUNTA part is in the columns part of the INDEX function and counts the number of columns by counting the top row for non blanks (5) So the range is $A$2:$E$8

2. COUNTER =COUNTA(INDEX(MasterList,,MATCH('List sample'!B5,Lists!$1:$1,0)))
This counts the non blank cells in the column indicated by the MATCH.....This makes the column dynamic and only shows occupied cells.

3. DVList =INDEX(MasterList,1,MATCH('List sample'!B5,Lists!$1:$1,0)):INDEX(MasterList,Counter,MATCH('List sample'!B5,Lists!$1:$1,0))

This takes the value from B5 and matches it against the column headers in the MasterList and the Counter is again used for the creation of the dynamic list.

I hope that this rambling explanation is somewhat understandable. It is sometimes easier to do than explain....like driving.

[bravobravoau]

Thanks again newdoverman - I know what you mean by trying to explain something like this. However it seems, true to your analogy, that I can't even walk let alone drive...

I have tried to follow your explaination a hundred times, but I continue to get errors. The attached sheet has the same as the number of 'options' that the live data will contain - would you be able to explain or show in that context?

Thanks heaps!!

[newdoverman]

Here is your file with the drop downs working correctly. The strange thing that I have found with the creation of drop down lists, especially the dynamic cascading type is that you have to be very careful to check that the cell references haven't changed on you while you are creating the name definitions. It helps to start off with creating a list that everything else will descend from then select a cell where the descendant list will be located....the cell references can still change so you have to be careful and check everything as you go along.

[bravobravoau]

Newdoverman, you are a marvel. I still don't understand a couple of parts of the dynamic lists, but I can work around that now. Thank you so much!!

[newdoverman]

Thank you for the feedback and good luck with your project.