Substituting multiple values for data validation

**Chemistification** · 01-03-2013, 08:05 AM

Hi all,

Happy new year to everyone.

Need a bit of help trying to substitute out values that i am using in data validation. From the sentence:

Consulting (Logistics, IT, Management)

I would like to remove the spaces, the (, the ) and the commas so that it reads:

ConsultingLogisticsITManagement

So that i can use it for data validation.

In the data validation box i am trying the formula:

=INDIRECT(SUBSTITUTE(substitute(substitute(substitute(c2," ",""),"(",""),")",""),",","")

But i keep getting told i have an error. Any ideas?

Many thanks,

C

**dilipandey** · 01-03-2013, 08:27 AM

Hi Chemist,

Why are you using Indirect here... ? suggest you to use the substitute formula and create a separate column and use that in validation. thanks.

Regards,
DILIPandey
<click on below * if this helps>

**Chemistification** · 01-03-2013, 11:02 AM

Hi DILIPandey,

Im using the indirect formula as i am trying to make data validation based on information in c2. ( i have attached a work book to explain it easier.)

1) The select a value from drop down list in c2
2) Options in c3 are relevant only to the value selected in c2

I am using named ranges but the issue i am having with is with removing the spaces and other bits so that the named range work

Does this help?

**dilipandey** · 01-03-2013, 03:22 PM

I would suggest you to try looking at Indirect function once again... it works on the text references and Indirect(ConsultingLogisticsITManagement) will not create any reference and hence you'll get error..

As per your sheet... you need a result in C3 based on C2 value.. and you can use some type of lookup function here based on your requirement

thanks.

Regards,
DILIPandey
<click on below * if this helps>

**Chemistification** · 01-07-2013, 05:40 AM

Morning,

I am not sure that i am able to do the lookup with the amount of potential values that could be in C2. There are probably 15 options (i have only shown a sample) that a user could select from a list in cell c2. Is it possible to construct a look up for this large a number?

Many thanks,

C

**Ace_XL** · 01-07-2013, 08:00 AM

Try this instead..

It makes use of a dynamic name range called 'List' for dependent data validation.

Some reading on dependent data validation
www.contextures.com/xlDataVal02.html

**Chemistification** · 01-07-2013, 12:43 PM

Great stuff. Combination of your spreadsheet and the one that you linked to and i managed to get it working.

Thanks to all that took the time to answer

C