INDIRECT function is not working when a cell value has Paranthesis()

**rakisa** · 08-09-2012, 01:35 PM

Hello,

I am trying to populate one column(drop-down) B based on the value selected in another column(drop-down value) A.
I am able to successfully achieve the functionality by doing appropriate mappings and using INDIRECT() function.
But, now the problem is, my cell values has parenthesis like "Marketing Services (T)" in A and in such cases, my drop-down in B is not being populated.
If I select a value from A which has no parenthesis\brackets, the corresponding values are getting populated in B appropriately.
It is not working only for the values having brackets.

I have tried to replace the brackets and space characters with underscore but still in vain. Your help is much appreciated !!
=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")," ","_"))
=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","_"),"(","_"),")","_"))

**NBVC** · 08-09-2012, 01:39 PM

Do you need to get rid of spaces too? What are the corresponding named ranges looking like?

**rakisa** · 08-09-2012, 01:52 PM

I dont want to remove spaces intentionally. But, if spaces are there the mapping does not work as intended and so by default I am replacing spaces with "_" everywhere and all that is working fine.
Examples for my mappings:
Please refer to my attachments that has two sheets
1. for entering data..and populating fields(Material Class L1,Material Class L2) accordingly in the drop down.
2. Mapping of the fields MCL1-MCL2

**NBVC** · 08-09-2012, 02:25 PM

You have inconsistent number of underscores in your named ranges... I suggest you go through and edit the names to make only single underscores between letters... then use data validation formula:

=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J2," ","_"),"(",""),")",""))

**rakisa** · 08-09-2012, 02:35 PM

I did not understand. I have no underscores in any of the cell values. I am trying to replace blank,Left brace and Right brace to underscore.
What do you mean by "edit the names to make only single underscores between letters"? Please provide an example as per the excel data which I have uploaded you in the before thread. Sorry for the inconvenience as I am new to the excel operations.

**NBVC** · 08-09-2012, 02:42 PM

If you go to the Formulas tab, then click Name Manager in the Defined Names section, you will see all your named ranges. You will note that many have multiple underscores. Select one, click Edit and make those double underscores into single underscores by deleting one....

Then the data validation should work.

In fact, you have also some entries in 1st Validation list with & sign, which is not allowed... so after making all the underscores into single underscores, change the data validation formula for second list to:

=INDIRECT(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J2," "," "),"(",""),")",""),"&"," "))," ","_"))