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.

  • You can use =INDIRECT(SUBSTITUTE(A1," ","_")) where A1 contain the Name Range – yass Apr 8 '17 at 18:42
  • so you suggest to replace the (_) with a space. Will try it and see. – Scorpion99 Apr 8 '17 at 18:43
  • Yes give it a try and tell us it will help other users – yass Apr 8 '17 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 '17 at 18:52
  • It worked, could you put it as an answer and explain as match as you can? – Scorpion99 Apr 8 '17 at 19:15

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

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

  • In my case, if I try to change the A8 to registered,B8 is not refreshing – Scorpion99 Apr 8 '17 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 '17 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 '17 at 19:26
  • If it is already a Named Range just click on the arrow it will show the corresponding list – yass Apr 8 '17 at 19:28

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.