Indirect()

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 :)
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
P. S.

Notice that the Trim() is doing nothing, since you are deleting the spaces.
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top