Excel dependent data validation =INDIRECT(SUBSTITUTE(C1," ","_"))

[springran]

Hi All

Please help

I have set up 3 level dependent data validations. I use =INDIRECT(SUBSTITUTE(C1," ","_")) in my 3rd level data validation ( under source ). The formula is working only with " " & "_" (two conditions).

I have " & ", "-" ,","in name range which I couldn't figure it out how to add extra conditions in the formula. example: if I select " apple & Veg" in 2nd level drop down the 3rd level doesn't working. On the other hand if I select a single word or " Apple Veg" in 2nd level, the 3rd level drop down has no problem.

Can anyone help?

 

[Crystalyzer]

please post a copy of your worksheet using the XL2BB tool