Solved Indirect Function with Special Characters

February 24, 2018 at 16:55:54
Specs: Windows 7
I'm using the "INDIRECT" function and noticed that it doesn't work for cells with special characters. For example, the list includes "Policy/Procedure". Do we have a workaround for this so that the dependent list can be recognized once this is selected?

See More: Indirect Function with Special Characters

Report •

✔ Best Answer
February 25, 2018 at 06:55:08
Now that I know what your actual issue is, I can offer a possible solution. I didn't what to just toss out random suggestions without knowing what problem I was trying to solve.

A Named Range can't contain spaces or slashes, but they can contain underscores.

Out Of Scope is not valid, but Out_Of_Scope is. So is Policy_Procedure.

The SUBSTITUTE function can substitute one character for another. e.g.

A1 contains Out Of Scope
B1 contains =SUBSTITUTE(A1," ","_")

We're asking the SUBSTITUTE function to replace any spaces in A1 with an underscore. Therefore the result in B1 will be Out_Of_Scope.

You can build a Nested SUBSTITUTE function to substitute more than one character

A1 contains Out Of Scope/In Scope
B1 contains =SUBSTITUTE(SUBSTITUTE(A1,"/","_")," ","_")

We're asking the SUBSTITUTE function to replace any spaces and any slashes in A1 with an underscore.

The result in B1 will be Out_Of_Scope_In_Scope

Now the good thing about the SUBSTITUTE function is that it does not cause an error if the character that you are asking it substitute is not present. It will do the substitution if the character is there, it will do nothing if the character isn't.

OK, so back to Named Range issue...

1 - Name your ranges People, Out_of_Scope and Policy_Procedure
2 - In the Data Validation source field for the Dependent Drop Down use this:

=INDIRECT(SUBSTITUTE(SUBSTITUTE(G1,"/","_")," ","_"))

Now when you chose Out Of Scope or Policy/Procedure from your main Drop Down, Excel will evaluate the SUBSTITUTE functions first, and then the INDIRECT function. Since the SUBSTITUTE function will place the underscores where appropriate, the INDIRECT function can now find the Named Ranges.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
February 24, 2018 at 18:29:04
The INDIRECT function works just fine for cells with special characters.

Enter Policy/Procedure in A1 and enter the following formula in any other cell. It will return TRUE.

=INDIRECT("A1") = "Policy/Procedure"

I think what you are trying to say (based on your use of the words dependent list is that you can't use special characters in a Range Name.

Is that what you mean?

message edited by DerbyDad03


Report •

#2
February 24, 2018 at 19:18:26
Sorry, I think you are correct. It's the dependent list. Everytime I select Policy/Procedure, it doesn't give me the correct list. Is this possible?

Report •

#3
February 24, 2018 at 19:38:25
Please take this in the spirit that it is intended, I'm trying to help and I need to understand your set up. Keep in mind that I can't see your workbook from where I am sitting.

Why do you have "Policy/Procedure" in the list of terms that create the dependent drop downs? You couldn't have created a named range with that name since the "/" would have given you an error. Why do you have a string that is not the name of a range in that list?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

Related Solutions

#4
February 24, 2018 at 20:00:16
Hey DerbyDad03. Sorry I'm not familiar with the terms, I just know basic Excel and I greatly appreciate your expertise and patience.

Here's what I have in Sheet 1.
A1 = Repeat Email Reason, A2:A4 = People, Policy/Procedure, Out of Scope
B1: People, B2:B3 = Skill, Knowledge
C1: Policy/Procedure, C2:C3 = Refer to branch, Research opened
D1: Out of Scope, D2:D3 = No process, System issue

Sheet 2
A1: Repeat Email Reason, A2:A10 (I was successful with the data validation placing drop down list)
B1: Repeat Email Details, B2:B10 (Whenever I choose, "Policy/Procedure" or "Out of Scope" from B2, the dependent list is not there unlike in "People" where I have "Skill" and "Knowledge".

I'm not sure if there's a better way to explain this, but I hope I did. Again, I'm not familiar with the Excel terms so I tried my best to explain it my way, and I hope you can visualize my workbook. Apologies for any inconvenience.



Report •

#5
February 24, 2018 at 20:26:10
I'm not getting it. A Dependent Drop List works like this:

Let's say you start with the set up I show below.

Your data is in A1:C3.

You have a Data Validation Drop Down in D1 and E1.

The source for D1's Drop Down is:

Dogs
Flowers
Spices

The source for E1's Drop Down is:

=INDIRECT(D1)

You would need to select A1:A3 and name the range Dogs.
You would need to select B1:B3 and name the range Flowers.
You would need to select C1:C3 and name the range Spices.

Since the source for the E1 Drop Down is =INDIRECT(D1) and D1 will contain the name of a range, the list in E1 will come from one of the named ranges chosen in D1.

e.g. If you chose Flowers from the Drop Down in D1, =INDIRECT(D1) will resolve to =B1:B3 (because B1:B3 has been named Flowers) and the E1 list will be populated with the items in B1:B3.

Is that what you are trying to do?


       A       B       C                D                        E
1    Pug     Rose     Salt        Drop Down List       Dependent Drop Down List
2    Lab     Pansy    Pepper
3    Chow    Tulip    Oregano

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#6
February 24, 2018 at 20:36:19
Yes this is what I'm trying to do. So say column A is "People", B is "Policy/Procedure" and C is "Out of Scope". In D, I select "People" it shows pug, lab, and chow. But when I select "Policy/Procedure" it doesn't give me rose, pansy and tulip. Same as when I select "Out of Policy" it won't give me salt, pepper, oregano. I guess that's what I meant for my title "Indirect Function With Special Characters" Seems like if there's a space or a special character, the list doesn't come out. I hope to God I am making sense now.


Report •

#7
February 24, 2018 at 20:58:45
re: "In D, I select "People" it shows pug, lab, and chow."

I'm not sure how else to explain this.

The only way to get a Dependent Drop Down list by choosing People is if, sometime in the past, you (or someone else) manually gave the range that contains the data for the list the name "People".

Do you know what a Named Range is? You (or someone) must have given a range the name People at some point otherwise the INDIRECT function would not have known where to get the data for the Dependent Drop Down from.

Try this: Select the cells that contain the data that show up in the "People" list (is that B2:B3?) Now look in the box above cell A1. Does it say "People"? If it does, then the Range that you selected has been given the name People which is why the INDIRECT function is able to populate the Dependent Drop Down.

OK, so assuming that you have a ranged named People, you should now understand why that Dependent Drop Down gets populated.

However, you can't possibly have a range named "Policy/Procedure" or "Out of Policy" because those are not valid range names. You can't use punctuation ("/") or spaces in a range name. That is where my confusion is coming from. It appears that you somehow ended up with range named "People" since that Dependent Drop Down works, yet you don't know why "Policy/Procedure" and "Out of Policy" don't work, which tells me that you don't know how to create a named range. If you tried to create a named range with those strings, you would have gotten an error right then and there and you wouldn't be asking a question about the INDIRECT function, you'd be asking us why you can't create the named ranges that you want to.

Again, your issue has nothing to do with the INDIRECT function, it has to do with the fact that the named ranges you are trying to use don't exist and they don't exist because those names aren't valid. The real question from my side is, "Why don't you know that those named ranges don't exist?"

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#8
February 24, 2018 at 21:56:13
Thank you. Again, I'm sorry if I'm kind of pain with this. I guess I asked the wrong question :-(. You're right when naming range, it doesn't work with Out of Scope and Policy/Procedure. I thought there's a workaround. Thank you for being so patient with me. I really want to learn I'm glad I have experts like you. Keep it up and more power :-)

Report •

#9
February 25, 2018 at 06:55:08
✔ Best Answer
Now that I know what your actual issue is, I can offer a possible solution. I didn't what to just toss out random suggestions without knowing what problem I was trying to solve.

A Named Range can't contain spaces or slashes, but they can contain underscores.

Out Of Scope is not valid, but Out_Of_Scope is. So is Policy_Procedure.

The SUBSTITUTE function can substitute one character for another. e.g.

A1 contains Out Of Scope
B1 contains =SUBSTITUTE(A1," ","_")

We're asking the SUBSTITUTE function to replace any spaces in A1 with an underscore. Therefore the result in B1 will be Out_Of_Scope.

You can build a Nested SUBSTITUTE function to substitute more than one character

A1 contains Out Of Scope/In Scope
B1 contains =SUBSTITUTE(SUBSTITUTE(A1,"/","_")," ","_")

We're asking the SUBSTITUTE function to replace any spaces and any slashes in A1 with an underscore.

The result in B1 will be Out_Of_Scope_In_Scope

Now the good thing about the SUBSTITUTE function is that it does not cause an error if the character that you are asking it substitute is not present. It will do the substitution if the character is there, it will do nothing if the character isn't.

OK, so back to Named Range issue...

1 - Name your ranges People, Out_of_Scope and Policy_Procedure
2 - In the Data Validation source field for the Dependent Drop Down use this:

=INDIRECT(SUBSTITUTE(SUBSTITUTE(G1,"/","_")," ","_"))

Now when you chose Out Of Scope or Policy/Procedure from your main Drop Down, Excel will evaluate the SUBSTITUTE functions first, and then the INDIRECT function. Since the SUBSTITUTE function will place the underscores where appropriate, the INDIRECT function can now find the Named Ranges.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Ask Question