How to Link a Cell Value with a Drop Down List in Excel (5 Ways)

The drop-down list is of great help when you are working with a large database and you need to pick a specific item from a list. Using a drop-down list you can select any specific data in seconds. You can also link a cell value with your drop-down list to make your task more comfortable. Today we will discuss how to link a cell value with a drop down list in Excel.

How to Link a Cell Value with a Drop Down List: 5 Unique Ways

Linking a cell value with your drop-down list makes the list more dynamic and efficient. All data relating to the list will then automatically update. Let’s learn 5 different ways to to link a cell value with an Excel drop down list.

1. Dynamic Drop-Down List to Link a Cell Value

The dynamic drop-down list will allow you to auto-update your data. In the following example, the “Item Name” of some computer accessories is given.

We will make a dynamic drop-down list from it.

Follow the steps below to utilize the method.

Steps:

• First of all, select a cell where you want to make the list (i.e. D5) and go to Data tab-> click on Data Validation.

• Then, the Data Validation dialogue box will appear.
• After that, select “List” as the validation criteria.
• In the source field, apply the OFFSET function along with the COUNTIF function. Insert the values and the final formula is:

• Then, click OK to get the drop-down list.

So we have got our drop-down list.

Now let’s check whether this list is linked with a cell value or not. Delete some data from your data list.

Then open the drop-down list and see that the list has auto-updated itself.

Similarly, if we add some values to the “Item” the list will auto add these values. So the cell values are linked with the list.

2. Dependent Drop-Down List to Link a Cell Value

The dependent drop-down list also can establish links to their cell values and auto-update. Let’s discuss this.

Consider this example where the “Hardware” and “Software” columns are given with some data. We need to make a dependent list from that.

Steps:

• First, select cell E5 and go to the Data tab ->select Data Validation from the Data Tools group.

• In the data validation dialogue box, select List as validation criteria. In the source field, assign the value =$B$4:$C$4 for selecting “Hardware” and “Software”.

• Now in the new window, click OK to make the list.

We have our drop-down list for the columns. To complete the task, select the “Hardware” and “Software” column, go to “Formula” and in the “Name Manager”, click on “Create From Selection”.

Formula → Name Manager → Create from Selection

• A new window popped out. Check on the “Top Row” and click OK.

• And two specific names are created. Click on “Close”.
• Now our final task is here. Select cell F4 and go to “Data Validation” and select “List”.

• In the “Source” box, apply the INDIRECT function:

This means that when you select “Hardware” in the drop-down list (E3), this refers to the named range “Hardware” (through the INDIRECT function) and thus lists all the items in that category.

• And click OK. The drop-down list is made and the cells are linked with the list.

• To prove that the cell values are linked with the list, you can add or subtract any value from the source list and can check that the drop-down list is auto-updating itself.

Read More: How to Create Drop Down List with Filter in Excel

3. Using VLOOKUP Function to Link a Cell

We can use the VLOOKUP function to link the cell with the drop-down list.

In the following example, the list of the “Item name” and their “Price” are given.

We will make a drop-down list, and link the cell values with the list using the VLOOKUP function.

Steps:

• First, make a drop-down list in the cell “E5” just like Method 1 using the source data as the cells of the “Item Name” column.

• Now the list is made. Here, apply the VLOOKUP function in the cell “F5”. The formula is:

• Next, press ENTER. The VLOOKUP function linked the cell values with the list.

You can change the list value and see if the cell values are linked or not.

Read More: How to Add Item to Drop-Down List in Excel

4. Using SUMIF Function to Link a Cell

The SUMIF function also can link the cell values with the list.

We will use the previous example. Now in the cell “F5” apply the SUMIF function.

After inserting the value, the formula is:

5. Hyperlink Drop-Down List to Link a Cell Value

In this method, we will make a hyperlink drop-down list. Let’s learn!

Here, we have two worksheets and name them “Hardware” and “Software”. One of them got data for “Hardware”.

And the other stands for the data of “Software”.

Steps:

• First, create another worksheet where we will make the hyperlink list. Make a “Category” column where you will insert the worksheet name that you want to link with the list. In the cell “E4” make the drop-down list of “Hardware” and “Software”.

• Select the drop-down list cell, go to “Formula”, and click on “Name Manager”.

•  In the newly popped window, click on “New” to give this cell a new name.

• Name this “E4” cell “Worksheet_Link” and click “OK”

• Select cell “E4” and press “CTRL+K” to create a link in this cell. In the new window, select “Place in the Document”, click on “Worksheet_Link” and press “OK”.

• Now, we will edit the link of this cell.

• Select the drop-down list cell, go to Formula, click on  Name Manager. Select Worksheet_Link and click Edit.

• In the Refers to field, insert the following formula,

Now the ADDRESS function is

• Click OK. Now the worksheets are linked with the drop-down list. Now let’s say we want to go to the “Hardware” sheet. Select “Hardware” from the list and click on it.

And we are in the hardware worksheet. That’s how we create a hyperlink drop-down list.

Read More: How to Create a Drop Down List From Another Sheet in Excel

Things to Remember

⏩ While creating a dynamic drop-down list, Make sure that the cell references are absolute (such as $B$4) and not relative (such as B2, or B$2, or $B2)

⏩ In the hyperlink drop-down list, the [abs_num], [a1] are ignored. Put (,,) to do that.

⏩ To avoid errors, remember to check “Ignore Blank” and “In-cell Dropdown”.

Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

Conclusion

So, we discussed five different methods to link a cell value with an Excel drop down list. A drop-down list linked with cell values makes our job a lot more easy and comfortable. If you have any confusion or thoughts regarding this article please let us know in the comment section.

Related Articles

• Create Excel Drop Down List from Table
• How to Edit Drop Down List in Excel
• How to Remove Drop Down List in Excel
• How to Create Excel Drop Down List with Color
• How to Create a Drop Down List with Unique Values in Excel
• How to Copy Filter Drop-Down List in Excel
• Excel Drop Down List Not Working

<< Go Back to Excel Drop-Down List | Data Validation in Excel | Learn Excel