![how to make a drop down list in excel table how to make a drop down list in excel table](https://i.stack.imgur.com/vJcHT.png)
Why should you put your data in a table? When your data is in a table, then as you add or remove items from the list, any drop-downs you based on that table will automatically update. To stop people from entering data that isn't in the drop-down list, click Stop. Information will show a message with this icon and Warning will show a message with this icon.
![how to make a drop down list in excel table how to make a drop down list in excel table](https://trumpexcel.com/wp-content/uploads/2013/11/select-multiple-items-from-a-drop-down-list-in-excel-Multiple-Selections.gif)
To show a message that doesn't stop people from entering data that isn't in the drop-down list, click Information or Warning. Not sure which option to pick in the Style box? If you don't want a message to show up, clear the check box.
![how to make a drop down list in excel table how to make a drop down list in excel table](https://nexacu.com.au/media/old-blog/2019-01-5.png)
If you want a message to pop up when someone enters something that's not in your list, check the Show error alert after invalid data is entered box, pick an option from the Style box, and type a title and message. If you want a message to pop up when the cell is clicked, check the Show input message when cell is selected box, and type a title and message in the boxes (up to 225 characters).
![how to make a drop down list in excel table how to make a drop down list in excel table](https://spreadsheetpoint.com/wp-content/uploads/2019/10/Example-of-a-drop-down-list-in-Google-Sheets.gif)
If it's OK for people to leave the cell empty, check the Ignore blank box. Note that we left out the header row, because we don't want that to be a selection option: We put ours on a sheet called Cities, in range A2:A9. On the Settings tab, in the Allow box, click List.Ĭlick in the Source box, then select your list range. Unlock specific areas of a protected workbook or stop sharing the worksheet, and then try step 3 again. Horizontal-Dynamic-Dependent-Drop-Down-List.Note: If you can't click Data Validation, the worksheet might be protected or shared. And if you have a very large spreadsheet the number of columns will become limiting long before the number of rows would. Obviously a standard Excel “Table” won’t work with this type of list. For one thing adding more data to the table will require inserting a column within the table range to avoid renaming the range. Practical ConsiderationsĪdding to each category list across columns is problematic. To enter an array formula, press ⌘+RETURN. To use values returned as an array, enter the INDEX function as an array formula in a horizontal range of cells for a row, and in a vertical range of cells for a column. If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. This comes from the Help section of the INDEX function where in Excel 2011 it reads: Here the zero means: return all columns (of row 2). If you want to return row 2 of the named range you would use =INDEX(myTable,2,0). The zero means: return all rows (of column 2). Normally, to return column 2 of a named range, you would use the following formula =INDEX(myTable,0,2). This is a simple case of using the INDEX function in a slightly different way. Two Ways to Use the INDEX Function to Return an Array And cell B2 will read the value in cell A2, match and return the proper row number, and return an array of values for that row. In cell B2, add a Data Validation list with the source being =myItemListH in the conditional drop down list from Excel.Ĭell A2 will give you a drop-down list of Fruit, Vegetables, or Other Stuff. In cell A2, add a Data Validation list with the source being =m圜ategoryH. Conditional Drop Down List (Excel)Ī defined name, myItemListH, is created with the following formula =INDEX(myTableH,MATCH(Sheet1!A2,m圜ategoryH,0),0) that will return a row that is matched by the contents of cell A2. At the end of the post I’ll give a link to the file I used. The screen shot below is what I’ll be referencing. I received a comment asking if a dynamic dependent drop-down list in Excel could have a list where the “table headers were actually rows and not columns?” Since I’ve already detailed how this is done in the article mentioned above, I’ll keep this short.