To do this, hover over the header until a black arrow shows up.

reference = instead of selecting an array of cells, the tables should be selected.

There are two syntax options for INDEX(). The INDEX() formula can be used to make sure the correct Table is used as reference. Setup formula for data validation criteria Tick the box to indicate that the table has headers.Ĭhange the table name to the division name.ĭo the same for the Games and Utility columns and rename them to TableGame and TableUtility. To do this, highlight each data column (CTRL + SHIFT + DOWN when selecting the header).ĭouble check if the cell referencing is correct. Instead of converting the entire data table as one Excel table, convert it into 3 Excel tables. Paste the formula to the source text box. To view the entire array, click inside the formula bar and press F9.Īdd data validation criteria to cell F5 through Data > Data validation. However, the formula actually returns an array and is unable to display all the values on a single cell. Since we only need one column, set this as 1.

An arbitrary number 20 is used for this example, which should be sufficient. Use a bigger height than what is expected to make sure additional cells are taken account of. The same MATCH() function will be used here: This will again depend on the division selected. cols = how many rows to the right until the first value to be used.rows = how many rows going down until the first value, 1.The OFFSET() function can also be used here. However, since the cell reference changes depending on the division selected by the user, another function should be introduced. The COUNTA() function comes in handy since it counts cells with numbers or text. This should correspond to the number of apps listed for each division and be dynamic to expand and contract. To fix this, a correction is made by deducting 1 from the MATCH() function. What this means is that when the Productivity division is selected, the MATCH() function returns a “1”, which tells the OFFSET() function to move one column to the right instead of staying in column A. However, this can’t be used as is since the return value of the MATCH() function will be 1, 2, or 3 depending on the selected division. lookup_array = where the lookup will be done, $A$4:$C$4.A MATCH() formula comes in handy since it returns a number: In this case, this is dependent on the selected Division by the user.

cols = how many columns to the right until the first value to be used.In this case, since we want to start one row below the header, we input 1 rows = how many rows going down until the first value to be used.Click inside the 'Source' text field, and write = the name you gave to the list (in our example: =cities).ĭone! Now you have a drop down list inside your selected cell.Whenever a formula is to be used as a data validation criteria, it is usually easier to first set the formula up on the spreadsheet, then transfer it to the data validation window so that all the Excel helper tips show up as guides. Click the words 'Any value' and choose 'List'.ħ. It will look like one of the two following examples, depending on your screen resolution:Ħ. Select the cell in which you want the drop down list to appear. Name the list, by selecting its range and writing its name in the name box. It is recommended to have it sorted alphabetically in ascending order, because this is the order in which they will appear in the drop down list.Ģ. Write the list somewhere inside the worksheet. Clicking the small arrow next to the cell, will open the list:Ĭreating a drop down list in Excel: Step by Stepġ. Here is an example of a drop down list that was created in cell B3. A drop down list enables you to choose a value from a list, instead of typing it.

Drop down list in excel