When creating a cell that contains a drop-down (data validation) list, the simplest way to create this is to reference the range that contains the data to be used in the drop-down.
This will result in a list that contains only these values
This works exactly as required but if we have lists that are regularly added to or items are removed we would have to change the static range that has been referenced.
If this doesn’t happen very often then it doesn’t really cause a problem, but if amendments to the list are required regularly or you have lots of drop-down cells then it becomes a bit of a chore to amend. And as we are all human and we make mistakes, you can guarantee that whilst having to amend several drop-down ranges, mistakes will be made and lists will be incomplete or contain blank options at the bottom of the list.
The solution to this is to create a dynamic list of items that can be referenced when creating the drop-down cell.
Firstly you will need to use the COUNTA formula in a cell to count the number of entries in a range.
Next we need to use the OFFSET formula when creating the drop-down cell
The OFFSET formula works as follows –
OFFSET(Start Cell, Number of rows to offset from the Start Cell for the 1st item in the list, Number of columns to offset from the Start Cell for the 1st item in the list, Range containing the value for the number of cells that contain values to be in the list).
Simplified this is –
=OFFSET(Start Cell, Row, Columns, Count)
Now when we add an item to the list, the drop-down automatically adds this to the list of available options.