Indirect()

[JeannetteE]

Hi,

I have 2 Columns, Column A is a value selected from a Drop down List, and Column B is also a value selected from a drop down list based on the value in Column A. I use an INDIRECT formula =INDIRECT(SUBSTITUTE(A2," ","")) to determine which values it brings back in the drop down from the relevant tables. This all works except for one of the values that has a comma in it (below in bold) so now I need to change the INDIRECT formula in Data Validation to something else. I tried =INDIRECT(SUBSTITUTE(TRIM(SUBSTITUTE(B55,"",",")),","," ") but this does not work, it works a cell without the INDIRECT but not in Data Validation.

Column A	Column B
Access Management	User Admin
Access Management	User Admin
Desktop Services	Windows OS
Internet, Email and Messaging	Internet
Internet, Email and Messaging	Email

<tbody>
</tbody>

I hope this makes sense

 

[pgc01]

I tried =INDIRECT(SUBSTITUTE(TRIM(SUBSTITUTE(B55,"",",")),","," ") but this does not work, it works a cell without the INDIRECT but not in Data Validation.

Hi

Strange, I don't understand the formula.

Shouldn't it be:

=INDIRECT(SUBSTITUTE(TRIM(SUBSTITUTE(B55," ",",")),",","")

Usually you define the names without deleting the spaces and other not allowed characters like commas.

Please clarify

 

[JeannetteE]

Hi

Strange, I don't understand the formula.

Shouldn't it be:

=INDIRECT(SUBSTITUTE(TRIM(SUBSTITUTE(B55," ",",")),",","")

Usually you define the names without deleting the spaces and other not allowed characters like commas.

Please clarify

Hi, thank for the reply. Your formula works if I add it to a cell without the INDIRECT, but if I add the whole formula to the Data Validation is gives me an error. I found the formula on line and it added comma's instead of taking out so I switched them around and it seemed to work when used in a cell.

 

[pgc01]

Hi

Works for me.

I did a quick test in Sheet2

Defined the name

Name: FirstSecond
Refers to: =Sheet2!$C$2:$C$4

In A1 I wrote "First, Second". Notice that it has a comma and a space.

In B1 I used in DataValidation->List:

=INDIRECT(SUBSTITUTE(TRIM(SUBSTITUTE(A1," ",",")),",",""))

and it works, I get the values $C$2:$C$4 in the Data Validation

 

[pgc01]

P. S.

Notice that the Trim() is doing nothing, since you are deleting the spaces.

 

[JeannetteE]

Hi

Works for me.

I did a quick test in Sheet2

Defined the name

Name: FirstSecond
Refers to: =Sheet2!$C$2:$C$4

In A1 I wrote "First, Second". Notice that it has a comma and a space.

In B1 I used in DataValidation->List:

=INDIRECT(SUBSTITUTE(TRIM(SUBSTITUTE(A1," ",",")),",",""))

and it works, I get the values $C$2:$C$4 in the Data Validation

Ok, so I did the same in a new spreadsheet and it works, however it's not in my main spreadsheet. I get the general if you are trying to add a formula error. So there must be something in the spreadsheet that is not right. I will look again and ss if I can find it.

Thanks for your help