Automatically updating cells from sourcebook to link book in excel
Apart from that, a dropdown prevents spelling mistakes and makes data input faster.
On the whole, there are 4 ways to make a drop down menu in Excel, and all of them have their own strong and weak points.
If for some reason you prefer a usual named range, then reference it using the OFFSET formula, as explained below.
As you see, the formula is comprised of 2 Excel functions - OFFSET and COUNTA.
This method works well for small Excel data validation lists that are unlikely to ever change.
If it's not the case, consider using one of the following options.
If you have created a named range based drop-down box, then you can just edit your range's items and then change the reference to the Named Range.
If you select the whole column, a drop down menu will be created in each cell of that column, which is a real time-saver, for example, when you are creating a questionnaire.
The COUNTA function counts all non-blanks in the specified column.
OFFSET takes that number and returns a reference to a range that includes only non-empty cells, starting from the first cell you specify in the formula.
In this case, your list will get updated automatically in all the cells that contain it, once you remove or add new entries to the source list.
The easiest way to create such a dynamically updated drop-down list in Excel is by creating a named list based on a table.Technically, this turns a drop-down list into an Excel combo box.