JeannetteE
Board Regular
- Joined
- May 19, 2016
- Messages
- 53
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.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Column A[/TD]
[TD="align: center"]Column B[/TD]
[/TR]
[TR]
[TD]Access Management[/TD]
[TD]User Admin[/TD]
[/TR]
[TR]
[TD]Access Management[/TD]
[TD]User Admin[/TD]
[/TR]
[TR]
[TD]Desktop Services[/TD]
[TD]Windows OS[/TD]
[/TR]
[TR]
[TD]Internet, Email and Messaging[/TD]
[TD]Internet[/TD]
[/TR]
[TR]
[TD]Internet, Email and Messaging[/TD]
[TD]Email[/TD]
[/TR]
</tbody>[/TABLE]
I hope this makes sense
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.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Column A[/TD]
[TD="align: center"]Column B[/TD]
[/TR]
[TR]
[TD]Access Management[/TD]
[TD]User Admin[/TD]
[/TR]
[TR]
[TD]Access Management[/TD]
[TD]User Admin[/TD]
[/TR]
[TR]
[TD]Desktop Services[/TD]
[TD]Windows OS[/TD]
[/TR]
[TR]
[TD]Internet, Email and Messaging[/TD]
[TD]Internet[/TD]
[/TR]
[TR]
[TD]Internet, Email and Messaging[/TD]
[TD]Email[/TD]
[/TR]
</tbody>[/TABLE]
I hope this makes sense
Last edited: