2

I am creating dependent drop down list in Excel using Data validation and Name Manager and the function Indirect.

My problem is that the Name Manager doesn't allow to have space when defining a new name. Based on Microsoft,this is not allowed and they suggest to put a (_) or (.) instead.

Define and use names in formulas

enter image description here

Is there any way to bypass this as I have many names having spaces to put them in the drop down menus.

Improve this question
6
  • You can use =INDIRECT(SUBSTITUTE(A1," ","_")) where A1 contain the Name Range
    – yass
    Apr 8, 2017 at 18:42
  • so you suggest to replace the (_) with a space. Will try it and see.
    – Scorpion99
    Apr 8, 2017 at 18:43
  • Yes give it a try and tell us it will help other users
    – yass
    Apr 8, 2017 at 18:44
  • Create the define Name with "_" Example key_one but in the list items write it without _ key one and use the formula
    – yass
    Apr 8, 2017 at 18:52
  • It worked, could you put it as an answer and explain as match as you can?
    – Scorpion99
    Apr 8, 2017 at 19:15

1 Answer 1

Reset to default
5

You can Define Name for your list with "_" instead of blank but in the List of items in Drop Down list just write space: 

Registered              first
Will be registered      second
                        third

This List Define Name Will_be_registered (first second third) and in Data Validation use:
List and for Source =INDIRECT(SUBSTITUTE(A8," ","_")) A8 is where you choose will be registered In C1 you will have the drop down (first second third)
You cannot use Name Manager with space but you can show your Data with space.

enter image description here

enter image description here

Improve this answer
6
  • In my case, if I try to change the A8 to registered,B8 is not refreshing
    – Scorpion99
    Apr 8, 2017 at 19:19
  • Is B8 where you choose will be registered or registered? it should be the first Drop down list options
    – yass
    Apr 8, 2017 at 19:21
  • Registered has to be Name of a list because of Indirect just select registered where you have it near the list and Name it registered also
    – yass
    Apr 8, 2017 at 19:26
  • If it is already a Named Range just click on the arrow it will show the corresponding list
    – yass
    Apr 8, 2017 at 19:28
  • Will this work if the item list (with substitution formula) has its own dependent drop-down menu ? (I.e. a third level)
    – Jeff Brower
    Apr 24, 2022 at 22:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .