How to Automatically Add New Items to a Drop Down List in Excel for Mac (2013)

In this article, we will explore the process of automatically adding new items to a drop-down list in Excel for Mac (2013). A drop-down list is a common feature in Excel that prevents users from entering invalid values in cells. It allows you to restrict the input values to a specific range or list.

To start, let's create two lists: one containing order numbers and the other containing products. We will use these lists to demonstrate how to build dependent drop-down lists.

Step 1: Create a Named Range

The first step is to create a named range for your lists. A named range is a great way to reference your lists, especially if you have large datasets that expand or change frequently. To create a named range, follow these steps:

* Press the 'Ctrl' key and click on the cell range containing your list.

* Select 'Define Name' from the dropdown menu.

* Enter a name for your range, such as 'OrderNumbers' or 'Products'.

* Click 'OK' to save the changes.

Step 2: Create Drop-Down Lists

Now that you have created a named range, let's create our drop-down lists. We will use the 'Data Validation' feature in Excel to achieve this.

* Select the cell where you want to insert the first drop-down list.

* Go to the 'Data' tab and select 'Data Validation'.

* In the 'Settings' section, select 'List' as the validation criteria.

* Enter the named range for your order numbers in the 'Source' field.

* Click 'OK' to save the changes.

Repeat the same steps to create a second drop-down list based on the chosen value in the first drop-down list. For example, if you choose an order number from the first list, the second list will display unique distinct values from column B that correspond to the chosen order number.

Step 3: Create a Formula with VLOOKUP

To link the two drop-down lists, we need to create a formula using the VLOOKUP function. The VLOOKUP function is used to search for a value in a table and return a corresponding value from another column.

* Select the cell where you want to display the value based on the second drop-down list.

* Enter the following formula: =VLOOKUP(B2, Products, 2, FALSE)

* Replace 'B2' with the cell reference of your first drop-down list.

* Replace 'Products' with the named range for your products list.

* Replace '2' with the column number containing the values you want to return.

* Click 'Enter' to save the changes.

Step 4: Update the Formula

When you update the value in the first drop-down list, the second drop-down list will automatically update based on the new value. To ensure this works correctly, we need to update our formula to reference the named range instead of a fixed cell reference.

* Select the cell containing the VLOOKUP formula.

* Press 'F9' to update the formula references.

* Click 'Enter' to save the changes.

In this article, we have demonstrated how to automatically add new items to a drop-down list in Excel for Mac (2013) using named ranges and the VLOOKUP function. By following these steps, you can create dependent drop-down lists that update dynamically based on user input.

Vlookup with drop down mac excel 2013