Here is an effective and simple technique to incorporate a dynamic approach to a drop-down list of inputs. It offers a more effective approach than creating a dynamic name range.
One. Select “Formulas” at the top of the page, then choose “Name Manager”.
Click on “New…” on the top left-hard corner.
Create a name to refer to the range of data. This range will be named “listBusUnits.”
Choose “Workbook”. Note: if you only want the name range to apply to the selected worksheet, select “Worksheet” instead. By default leave “Comments” blank, and with “Refers to:” type the following formula:
The first element of the OFFSET formula, the “reference” must cite the first value in the list – in this example it is “Alberta Mining Ltd” in cell B12.
Remember in the next two elements, “rows” and “cols”, we must leave them blank or enter zero values (as per the above). Given the list runs vertically, we must insert the subsequent formula into “[height]”:
It has to reference the entire range of cells, which you need to reference, as well as blank cells that you might possibly use in the future – when you want to add to the existing list. Don’t forget to reference the first cell above the first Business Unit, “Alberta Mining Ltd”; which in this example is cell B11.
The final part of the formula, “[width]”, needs to remain blank.
The selected data array referenced in the formula must be free of other information. The real value of this approach, in creating a dynamic drop-down list, is the capacity to seamlessly update (add or remove) the inputs in the data range.
Trying out the Drop-down List
As per the following, selecting a cell(s), choose “Data” and then “Data Validation” and choose “List” under “Settings”.
Then enter the name of the list, “listBusUnits” in “Source”.
Now test the drop-down list out, by adding some further names in cells B16 and B17, and observe how the drop-down list seamlessly and dynamically updates to signify these new additions!