space in the name range

[BittenApple]

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

[HansV]

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).

[BittenApple]

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

[HansV]

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?