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

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

6

1 Answer

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.

6

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy