The OFFSET function returns an array that starts from a specified reference and has specified height and width numbers. Using COUNTA function to return row count gives the entire formula the dynamic behavior we are looking for We are using the OFFSET function's ability to return arrays to generate dynamic lists.
Type your name into Source box, with an equal sign =List.Click Data Validation under DATA tab in ribbon.Select the cell that will contains the drop down.Type ) to close the OFFSET function and finish the formula.Select the whole column that includes the list 'Create dynamic lists'!$B:$B.Continue with COUNTA( function to count filled cells.Type 0, to make the list start in the same column.Type 1, to make the list start at the next row, after the title.Select title of your list 'Create dynamic lists'!$B$2,.Start formula with the =OFFSET( function.Enter a name and clear Refer to formula box.Click Define Name under FORMULAS tab in ribbon.=OFFSET(title of list, 1, 0, COUNTA(column that includes the list)-1) Steps This article shows you how to create a dynamic drop down list with the help of OFFSET and COUNTA functions. Creating a dynamic Excel drop down list is very useful If you have a list that is updated frequently.
0 kommentar(er)
