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
1	Location	Corby Graduate	Corby Placement	Hartlepool Graduate	Hartlepool Placement	Port Talbot Graduate	Port Talbot Placement
2	Corby	Procurement	Mechanical	Electrical	N/A	Electrical	Group Environment
3	Hartlepool			Mechanical		Finance
4	Port Talbot					Group Environment
5	S****horpe					Process Control
6	Shotton					Research&Development
7	Stocksbridge					Sales&Marketing
8	Teeside Skinningrove
9	Trostre
10	UK Various
11	Wolverhampton

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Background Data​

And this is what the main sheet looks like:

Excel 2010

	A	B	C	D	E	F	G
1	Candidate ID	First Name	Surname	Location Choice 1	Location Choice 2	Scheme	Function Choice (Input) Discipline
2	1234567	John	Doe	Port Talbot		Placement

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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