It is necessary to make a drop-down list with values from the dynamic range. Drop-down list with data lookup in Excel Assign a name for a range of values and enter the name in the «Source:» field.Īny of the mentioned options will give the same result.Specify a range of cells with a list as a source.
![how to add drop down menu in excel 2016 how to add drop down menu in excel 2016](https://4.bp.blogspot.com/-lP-pitlqJR4/VrXidDzNXtI/AAAAAAAALs0/6Qa9x73JFUA/w1200-h630-p-k-no-nu/Title.png)
Manually through the «Comma» in the «Source:» field.You can enter the values from which the drop-down list will consist, in different ways: In cases like this where there is only one row in the array, you can skip the row_num argument entirely.Įxcel will automatically understand it as blank and consider the next argument to be the column_num.Path: the «DATA» menu – the «Data Validation» tool – the «Settings» tab. If you get a “0” in empty cells, you can hide them with an IF() formula or use custom formatting “# -# ” The row_num argument is ignored since there is only one row. The array doesn’t need to be fixed with the $ symbol since you want the array to move down when the formula is dragged down to the last row. Instead, the array should reference to the specific row. Since you want the list values in separate cells, the array to be used should not be that of the entire table. In Cell I5, you want to display the contents of the first row, depending on the selected Div group in cell I5. The INDEX-MATCH approach can be used to display the list. Using the same technique, there is also an option to display the list in separate cells.Ĭreate the first dropdown list using the same method above or copy cell G4 to cell I4. Go to Data Validation, select List, and in Source, paste into the Data Validation list.Ĭlick OK and you will see that the second dropdown list changes according to the chosen option for the first dropdown. Go to the cell where you want the second dropdown list, cell G5. Once you get the results needed, highlight the entire formula, right click and select Copy (or press CTRL C). Press ESC or CTRL Z to leave this view and revert back to the normal formula. To check if it returns the correct list, go to the formula bar and hit the F9 key.
#How to add drop down menu in excel 2016 full
When you hit ENTER, the value will not show the full list result of the formula, but instead only display the contents of one of the cells. (Here is a link to other INDEX MATCH guides.)
![how to add drop down menu in excel 2016 how to add drop down menu in excel 2016](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/data-validation-window-in-excel.png)
Since this will be pasted to Data Validation, make sure to fix the cell references by adding $ or pressing the F4 key.
![how to add drop down menu in excel 2016 how to add drop down menu in excel 2016](https://pdfimages.wondershare.com/word/word-add-drop-down-list-02.jpg)
array: The area where the answer could be.One option is to write the formula in a random cell in the spreadsheet and then copy and paste that as the Source in the Data Validation window. However, writing the formula directly on Source box of the Data Validation window will not give you the Excel help to assist you with the syntax. Instead, a combination of Excel’s INDEX() and MATCH() function will be used. However, unlike the first dropdown list, a direct cell reference can’t be used for the second list since the source will be different depending on the selected option in the first list. In the same way, go to Data Validation under the Data tab and select List.