Dependant drop-down list stopped working

[Fishboy]

Hi all,

In my Excel 2010 workbook I have created some dependant drop-down lists. On a hidden sheet called 'Background Data' I have the various dependencies set out in tables which have been given named ranges.

Initially everything was working fine on my test data, but when I started adding in live information to the mix one of the dependencies stopped working and I cannot figure out why as nothing has changed other than the actual data on the main sheet.

This is a snippet of the dependency table that has stopped working (the bits not included are basically just more of the same). The named ranges are basically the header titles with underscores instead of spaces.

Excel 2010

	A	B	C	D	E	F	G

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #92D050, align: center"]Location[/TD]
[TD="bgcolor: #92D050, align: center"]Corby Graduate[/TD]
[TD="bgcolor: #92D050, align: center"]Corby Placement[/TD]
[TD="bgcolor: #92D050, align: center"]Hartlepool Graduate[/TD]
[TD="bgcolor: #92D050, align: center"]Hartlepool Placement[/TD]
[TD="bgcolor: #92D050, align: center"]Port Talbot Graduate[/TD]
[TD="bgcolor: #92D050, align: center"]Port Talbot Placement[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Corby[/TD]
[TD="align: center"]Procurement[/TD]
[TD="align: center"]Mechanical[/TD]
[TD="align: center"]Electrical[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"]Electrical[/TD]
[TD="align: center"]Group Environment[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Hartlepool[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Mechanical[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Finance[/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Port Talbot[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Group Environment[/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]S****horpe[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Process Control[/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Shotton[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Research&Development[/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]Stocksbridge[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Sales&Marketing[/TD]
[TD="align: center"][/TD]

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

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

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

[TD="align: center"]11[/TD]
[TD="align: center"]Wolverhampton[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

Background Data​

And this is what the main sheet looks like:

Excel 2010

	A	B	C	D	E	F	G

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #000000, align: center"]Candidate ID[/TD]
[TD="bgcolor: #000000, align: center"]First
Name[/TD]
[TD="bgcolor: #000000, align: center"]Surname[/TD]
[TD="bgcolor: #000000, align: center"]Location
Choice 1[/TD]
[TD="bgcolor: #000000, align: center"]Location
Choice 2[/TD]
[TD="bgcolor: #000000, align: center"]Scheme[/TD]
[TD="bgcolor: #000000, align: center"]Function Choice
(Input) Discipline[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1234567[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]Doe[/TD]
[TD="align: center"]Port Talbot[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Placement[/TD]
[TD="align: center"][/TD]

</tbody>

Screening - New​

So, in column G the data validation rule (that was originally working but stopped) is as follows:

=INDIRECT(SUBSTITUTE($D$2," ","_"&"_"&$F$2))

Which when working took the value of D2, substituted any spaces for an underscore, added and underscore at the end, then took the value of F2.

In the example data this would have generated 'Teeside_Skinningrove_Placement' which as you can see should have tied in with the named range 'Teeside_Skinningrove_Placement' and therefore the next drop-down box should have had "Group Environment" as the only available option.

Instead the final drop-down just refuses to drop down as if there are no valid options to be selected from.

Does anyone have any ideas what may be up?

 

[Fishboy]

Just noticed the error in my description. The example data would have returned 'Port_Talbot_Placement'...

Regardless, this has no effect on the issue.

 

[Fishboy]

Hmmm, I can get it all working again if I forgo the substitution part, but that means removing the spaces from all of the other data which is really not ideal.

I am hoping at some point one of the experts looks at this thread, as so far I have had a load of views and no advice