Indirect "Substitute(Substitute(" not working for multiple spaces

[Ochimus]

Col B has a Dynamic list of Categories.

Col D has a Dynamic List of elements for the first Category ("Physical and organoleptic requirements").

I can select the Category from the DropList in G2, which should then populate the Dependent DropList in G4.

Because there are multiple spaces in the Category Name I know normal "Indirect(Substitute" won't work

Tried the following formula in G4, but doesn't work either:

=INDIRECT(SUBSTITUTE(SUBSTITUTE(G2," ","")))

All solutions, suggestions and pointers welcomed as ever

Ochimus

[azumi]

Rather than using INDIRECT function i'm just using simple INDEX and MATCH formula:

=INDEX($D$2:$D$6,MATCH($G$2,$B$2:$B$6,0))

[Fluff13]

Using Tables
with =INDIRECT(SUBSTITUTE(G2," ","_"))

[BadlySpelledBuoy]

Excel doesn't like dynamic named ranges in conjunction with INDIRECT/SUBSTITUTE to create dependent drop down lists so you can use tables to create the dynamic range in stead.

EDIT: Fluff13's solution is the same as mine. Pipped to the post before I could add my attachment!

BSB