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”.

Select “Name Manager”

Click on “New…” on the top left-hard corner.

Choose “New…” on the top left-hard corner

Create a name to refer to the range of data. This range will be named “listBusUnits.”

New Name

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:

=OFFSET(Tableau_BO!$B$12,0,0,COUNTA(Tableau_BO!$B$11:$B$40)-1,)

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.

OFFSET formula

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]”:

COUNTA(Tableau_BO!$B$11:$B$40)-1

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.

COUNTA formula

Please note

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”.

Choose “Data” and then “Data Validation”, and choose “List” under “Settings”

Data Validation window

Then enter the name of the list, “listBusUnits” in “Source”.

Then enter the name of the list, “listBusUnits” in “Source”

Drop-down list

Final Remark

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!

See how the drop-down box seamlessly and dynamically updates to reflect these new additions