Hello team,
I created a dependent dropdowns and they worked well.
I had to change the name of the header that contains spaces. My last drop down doesn't work any longer.
My range was B and my last dropdown worked perfect and now it has been turned into "BBB BBB BBB", it doesn't work.
I edit the old name but I have to put underscore for a new header while excel contents don't have underscore.
I need to keep the space, otherwise i have to change a dash for products, then data would be changed. I need to keep products as they are in the excel sheet.
Any help?
Regards,
BittenApple
space in the name range
-
- Administrator
- Posts: 78402
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: space in the name range
If you have an item BBB BBB BBB in the dropdown list of cell C2, for example, name the corresponding range BBB_BBB_BBB. You can then use
=INDIRECT(SUBSTITUTE(C2," ","_"))
as source for the data validation dropdown in the cell next to it (D2 in this example).
=INDIRECT(SUBSTITUTE(C2," ","_"))
as source for the data validation dropdown in the cell next to it (D2 in this example).
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: space in the name range
Hello Hans,
Thanks for the response.
If I use indirect and include underscore in my value, then I don't have that value in the table with under_scores.
Hospitals Hospital_blue
Hospital blue a
Hospital bb b
san joe Hospital c
San Diego and Hoag
In my table for Hospitals column, I have 10 entries which are repeated down the column.
If I put a underscore in the Hospital blue to be able to create a named range for Hospital_blue which in turn maps to diagcode, then Hospital_blue entered as a name for a defined name range are not same with the Hospital blue in the table.
Can you please explain a little bit about the new function? I can use that until, I found a solution. I don't know what happened to that post, I can't find it.
- How about the check boxes or radio button? Then we don't have to build third dropdowns.
Regards,
BittenApple
Thanks for the response.
If I use indirect and include underscore in my value, then I don't have that value in the table with under_scores.
Hospitals Hospital_blue
Hospital blue a
Hospital bb b
san joe Hospital c
San Diego and Hoag
In my table for Hospitals column, I have 10 entries which are repeated down the column.
If I put a underscore in the Hospital blue to be able to create a named range for Hospital_blue which in turn maps to diagcode, then Hospital_blue entered as a name for a defined name range are not same with the Hospital blue in the table.
Can you please explain a little bit about the new function? I can use that until, I found a solution. I don't know what happened to that post, I can't find it.
- How about the check boxes or radio button? Then we don't have to build third dropdowns.
Regards,
BittenApple
-
- Administrator
- Posts: 78402
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: space in the name range
You now have 3 threads about the same subject. I will lock this one. Please refer to How to create 3 dependent List boxes for reporting?
Best wishes,
Hans
Hans