dropdownlistmicrosoft excelmicrosoft-excel-2010
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.
Easy way in 4 simple steps
Step 1 Create your tables:
Step 2
Name your tables: Select the tables and rename them, so that it matches the value in the primary table Table1, eg. Table2 with the header "Cakes" should be named Cake, since that's the value in the primary table Table1.
But how? Click the corner of the table to select it, enter a new label where it says Table2 or similar and press enter. Don't worry if it still says "Table2" etc.
(Note! This is not necessary for the primary table eg. Table1.)
Step 3
Select where you want your first list, go to the data tab and into data validation.
Select "list" and enter: =INDIRECT("Table1") in the source window:
Step 4
Select a cell for the second list, that will be dependent on the first list.
Return to data validation, choose "list" and enter =INDIRECT(K2) where "K2" is the location of the first list.
DONE
For a third list, dependent on the second list, repeat step 4 and refer to the second list instead. Good luck!
Like you said you can use another cell M1 for example :
=If(M173=True,"Fine","No")
I used Fine just in case you need Yes in another place.
Select the options of your Dropdown List and define Name Fine
In K72 create Data Validation, choose List
In Source write:
=Indirect(M1)
In that case when M1 is different from Fine the dropdown is empty, when you have Fine in M1 K72 will show the values that you want to choose from the list
Update for Drop Down List become blank
Define name a blank cell No make it in a special place so you don't use it for any value (near the Fine list or yes list)
Keep the data validation Source like before =Indirect(M1)
If M1 = "No" the List will become blank (you have to choose the blank to clear the cell if it has any value)
If M1="Yes" or "Fine" whatever you choose the List will have the options you need
Just use normal word for Define Name do not use TRUE, False...
To Clear it automatically you will need VBA
Best 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:
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.