Ever wonder how to create dynamic dropdown list? No more changing the data validation->list->source when you add or delete items from the list.

Question

Hint: use OFFSET to create dynamic dropdown lists? Create once and then rest assure the dropdown list will stay up to date with the latest set of items you want to display in the dropdown

Answer 1

You can also use a SORT(UNIQUE()) function to reference a column or table of cells to create a dynamic drop-down list that will update as the data changes in that column. In a cell, type in =SORT(UNIQUE(column or range)) to make a dynamic list. After that you can make a drop down list anywhere in you workbook. Go to the data validation box and type in your source as a spill range reference where you add # to the end of the cell name that you entered the SORT(UNIQUE()) function in. For example it would look like =$A:$1# in the source box. Make sure you check the ignore blank box so your list doesn't include blank cells.