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

hetting result

The drop-down list is a great helper 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 in this article we will discuss how to link a cell value with a drop-down list in Excel.

Download Practice Workbook

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

Link a Cell Value with a Drop Down List (5 Ways)

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

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

The dynamic drop-down list will allow you to auto-update your data. Follow these steps to learn.

Step-1:

In the following example, the “Item Name” of some computer accessories is given. We will make a dynamic drop-down list from it.

creating table

Step-2:

Select a cell where you want to make the list (D4) and go to “Data”, click on “Data Validation”

Data → Data Tools → Data Validation

creating table

In the data validation dialogue box, select “List” as the validation criteria. Click on this icon to continue.

selecting list

Step-3:

In the source field, apply the “OFFSET” formula. Insert the values and the final formula is

=OFFSET($B$4,0,0,COUNTIF($B$4:$B$100,”<>”))

Where,

  • Reference is $B$4
  • Rows and Columns is 0
  • [height] is COUNTIF($B$4:$B$100,”<>”)

selecting list

Click “OK” to get the drop-down list.

hetting result

Step-4:

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.

testing result

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

final result

Step-5:

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.

final result

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.

Step-1:

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

creating table

Step-2:

Select cell “E4” and go to “Data”, “Data Validation”

Data → Data Tools → Data Validation

creating table

Step-3:

In the data validation dialogue box, select “List” as validation criteria. Open the source field and select “Hardware” and “Software”. Click on this icon icon to continue.

getting result

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

getting result

Step-4:

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

selecting items

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

selecting items

And two specific names are created. Click on “Close”.

name range

Step-5:

Now our final task is here. Select cell F4 and go to “Data Validation” and select “List”.

naming the column

Step-6:
In the “Source” box, apply this formula,

=INDIRECT(E4)

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.

creating list

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

getting result

Step-7:

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.

final result

3. Using the VLOOKUP Function to Link a Cell

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

Step-1:

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.

creating table

Step-2:

Make a drop-down list in the cell “E4” using the source data as the “Item Name”.

drop down list

Step-3:

Now the list is made, apply the “VLOOKUP” function in the cell “F4”. The formula is

=VLOOKUP(E4,B4:C23,2,FALSE)

Where,

  • Lookup_Value is E4 (The drop-down list)
  • Table_Array is B4:C23
  • Col_Index_Num is 2
  • We want the exact value (FALSE)

applying formula

Press “Enter”. The “VLOOKUP” function linked the cell values with the list.

testing formula

Step-4:

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

final result

4. Using the SUMIF Function to Link a Cell

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

Step-1:

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

After inserting the value, the formula is

=SUMIF(B4:B23,E4,C4:C23)

Where,

  • Range is B4:B23
  • Criterion is E4
  • [Sum_range] is C4:C23

applying formula

Step-2:

Press “Enter”. The cells are now linked with the list.

getting result

Step-3:

You can change the list reference to check the link between the cells.

final result

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

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

Step-1:

First, make two new worksheets and name them “Hardware” and “Software”. In the “Hardware” sheet, the data is

creating table

And for the “Software” sheet,

creating table

Step-2:

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 “E3” make the drop-down list of “Hardware” and “Software”.

drop down list

Step-3:

Select the drop-down list cell, go to “Formula”, click on “Name Manager”. In the newly popped window, click on “New” to give this cell a new name.

adding name

Step-4:

Name this “E3” cell “Worksheet_Link” and click “OK”

editing name

Step-5:

Select cell “E3” 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”.

creating link

Step-6:

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

editing reference

Step-7:

In the reference cell, Insert this fromula,

=INDIRECT(ADDRESS(1,1,,,E3))

Where,

  • [ref_text] is ADDRESS(1,1,,,E3)

Now the “ADDRESS” function is

=ADDRESS(1,1,,,E3)

Where,

  • Row_num is 1 for the linked worksheet.
  • Col_Num is 1 for the linked worksheet
  • [abs_num], [a1] is ignored
  • [sheet_text] is E3.

editing reference

Step-8:

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.

getting result

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

final result

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

Conclusion

A drop-down list linked with cell values makes our job a lot more easy and comfortable. We discussed five different methods to do that. If you have any confusion or thought regarding this article please let us know in the comment section.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo