The drop down list works as seen in the two previous examples, but offers the added advantage of being updated whenever choices are added to the end of the list on the Source worksheet. The result will be only one column wide, so 1 is the final argument in our formula. The height of our result should be whatever the height of the list is, so we ask Excel to count the number of values within the list by using a COUNTA formula, making reference to the entire column A. Since the result that we want should begin with the reference cell, the offset should remain at zero rows, and zero columns (away from the starting point). The above formula tells Excel to use cell A2 in the Source worksheet as the starting point (note the absolute reference to cell A2). = OFFSET ( Source ! $A$2, 0, 0, COUNTA ( Source ! $A : $A ), 1 )
The source data of club names will come from another sheet (Source) within this workbook. We want columns B and C on the Games worksheet to display a drop down list of all the clubs within the league. To make this easier to follow, we’ll use the OFFSET function to create a dynamic drop down list in the following example. If omitted, the width of reference is used. Width - The number of columns that the result should contain.If omitted, the height of reference is used. Height - The number of rows that the result should contain.As well as rows, cols can be positive (to the right of the starting reference) or negative (to the left of the starting reference). Cols - The number of columns you want the formula to move from the starting point.In the case of a negative number, it goes upward from the starting reference. If rows is a positive number, the formula moves downward from the starting reference. Rows - This is the number of rows (down or up) to move from the starting point.Reference - This is a cell or range of cells (adjacent) from which you can base the offset.
The first three arguments are required and the last two are optional. The syntax of the OFFSET function is: = OFFSET (reference, rows, cols, , )
You can also use the OFFSET formula to create dynamic drop down lists, which automatically update when items are added to the end of the list. Method 3 - OFFSET formula (dynamic drop down lists) Each item listed is shown in a different line in the drop down menu. This will create a drop down list in the selected cell(s).
We’ll explore how to create a drop down list in Excel using three methods. You can make a drop down list in Excel in a variety of ways. They are very user-friendly and are a great way to reduce input errors. Excel drop down lists are an easy way to control the values which are entered in a cell.