Forum Discussion
Data Valication - Drop down list
I created a short list with 2 items I want users to choose from on a separate sheet in my workbook. I went through the steps to create the dropdown, selected the cells, created a table but nothing appears when I select a cell in my workbook and right click to get "Pick from Dropdown List" nothing comes up. How can I see my list?
It sounds like you created a table, and that's all. When you right click, and select Pick From Drop-down List, Excel automatically provides a list of the values that are entered in the cells immediately above the one you selected, and allows you to pick from those. In order to accomplish what you are trying to create, you need to select the cells where you want the drop-down list to be available to the users, go to the Data tab, and click on Data Validation, and select Data Validation.... in that window, change the Allow: drop-down to List, and in the Source field, enter =indirect("Table1[Header]"), where table 1 is the name of the table you have your values in, and Header is the name of the column. If you want the user to be able to type their own response, go to the Error Alert tab, and uncheck the "Show error alert after invalid data is entered" checkbox and then click OK. You did not provide an example of what you have, so it's difficult to provide more info than that.
I hope the attached can help..
thank you
Sorry, I don't understand. You have 2 sheets: MAIIN and Data. I only have one column with a header and 2 values. Do I need to repeat another column? In which sheet? Both of them? In the MAIN sheet you have a drop down arrow in column B. I cannot get that option in my data. I also do not have a column B. Can you include some words as well as pictures for the explanation?
Also, is it possible for the dropdown list to be in the worksheet with all the other information? This makes more sense to me so the user can populate from one of 2 choices. I see the dropdown list I created on the additional worksheet but not on the main worksheet.
It sounds like you created a table, and that's all. When you right click, and select Pick From Drop-down List, Excel automatically provides a list of the values that are entered in the cells immediately above the one you selected, and allows you to pick from those. In order to accomplish what you are trying to create, you need to select the cells where you want the drop-down list to be available to the users, go to the Data tab, and click on Data Validation, and select Data Validation.... in that window, change the Allow: drop-down to List, and in the Source field, enter =indirect("Table1[Header]"), where table 1 is the name of the table you have your values in, and Header is the name of the column. If you want the user to be able to type their own response, go to the Error Alert tab, and uncheck the "Show error alert after invalid data is entered" checkbox and then click OK. You did not provide an example of what you have, so it's difficult to provide more info than that.
