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.

I need to generate a dependant list, however the 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.

[TABLE="width: 272"]
<TBODY>[TR]
[TD][/TD]
[TD]A</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD]Division</SPAN>
[/TD]
[TD]Section</SPAN>
[/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6</SPAN>
[/TD]
[TD]Divisions</SPAN>
[/TD]
[TD]RANGE:
"Divisions"</SPAN>
[/TD]
[/TR]
[TR]
[TD]7</SPAN>
[/TD]
[TD]Finance</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8</SPAN>
[/TD]
[TD]Human Resources</SPAN>
[/TD]
[/TR]
[TR]
[TD]9</SPAN>
[/TD]
[TD]Facilities & Security</SPAN>
[/TD]
[/TR]
[TR]
[TD]10</SPAN>
[/TD]
[TD]Operations</SPAN>
[/TD]
[/TR]
[TR]
[TD]11</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12</SPAN>
[/TD]
[TD]Finance</SPAN>
[/TD]
[TD]RANGE:
"Finance"</SPAN>
[/TD]
[/TR]
[TR]
[TD]13</SPAN>
[/TD]
[TD]Accounts</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14</SPAN>
[/TD]
[TD]Reporting</SPAN>
[/TD]
[/TR]
[TR]
[TD]15</SPAN>
[/TD]
[TD]Strategic Advice</SPAN>
[/TD]
[/TR]
[TR]
[TD]16</SPAN>
[/TD]
[TD]Banking</SPAN>
[/TD]
[/TR]
[TR]
[TD]17</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18</SPAN>
[/TD]
[TD]Human Resources</SPAN>
[/TD]
[TD]RANGE:
"HumanResources"</SPAN>
[/TD]
[/TR]
[TR]
[TD]19</SPAN>
[/TD]
[TD]Employee Entitlements</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20</SPAN>
[/TD]
[TD]Conduct</SPAN>
[/TD]
[/TR]
[TR]
[TD]21</SPAN>
[/TD]
[TD]Analytics</SPAN>
[/TD]
[/TR]
[TR]
[TD]22</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26</SPAN>
[/TD]
[TD]Facilities & Security</SPAN>
[/TD]
[TD]RANGE:
"FacilitiesANDSecurity"</SPAN>
[/TD]
[/TR]
[TR]
[TD]27</SPAN>
[/TD]
[TD]Facilities Management</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28</SPAN>
[/TD]
[TD]Help Desk</SPAN>
[/TD]
[/TR]
[TR]
[TD]29</SPAN>
[/TD]
[TD]Personnel Security</SPAN>
[/TD]
[/TR]
[TR]
[TD]30</SPAN>
[/TD]
[TD]Protective Security</SPAN>
[/TD]
[/TR]
[TR]
[TD]31</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36</SPAN>
[/TD]
[TD]Operations</SPAN>
[/TD]
[TD]RANGE:
"Operations"</SPAN>
[/TD]
[/TR]
[TR]
[TD]37</SPAN>
[/TD]
[TD]Team 1</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]38</SPAN>
[/TD]
[TD]Team 2</SPAN>
[/TD]
[/TR]
[TR]
[TD]39</SPAN>
[/TD]
[TD]Team 6</SPAN>
[/TD]
[/TR]
[TR]
[TD]40</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

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

 

[mohan.pandey87]

Not clear could you please attached file with some dummy data. It would be more helpfull to understand.

 

[BravoBravoAu]

I'm not able to attach a file!!