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.
In the following example, the “Item Name” of some computer accessories is given. We will make a dynamic drop-down list from it.
Select a cell where you want to make the list (D4) and go to “Data”, click on “Data Validation”
Data → Data Tools → Data Validation
In the data validation dialogue box, select “List” as the validation criteria. Click on this icon to continue.
In the source field, apply the “OFFSET” formula. Insert the values and the final formula is
- Reference is $B$4
- Rows and Columns is 0
- [height] is COUNTIF($B$4:$B$100,”<>”)
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.
And 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.
Consider this example where the “Hardware” and “Software” columns are given with some data. We need to make a dependent list from that.
Select cell “E4” and go to “Data”, “Data Validation”
Data → Data Tools → Data Validation
In the data validation dialogue box, select “List” as validation criteria. Open the source field and select “Hardware” and “Software”. Click on this icon to continue.
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 this formula,
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.
3. Using the 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.
Make a drop-down list in the cell “E4” using the source data as the “Item Name”.
Now the list is made, apply the “VLOOKUP” function in the cell “F4”. The formula is
- Lookup_Value is E4 (The drop-down list)
- Table_Array is B4:C23
- Col_Index_Num is 2
- We want the exact value (FALSE)
Press “Enter”. The “VLOOKUP” function linked the cell values with the list.
You can change the list value and see that the cell values are linked or not.
4. Using the 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 “F4” apply the “SUMIF” function.
After inserting the value, the formula is
- Range is B4:B23
- Criterion is E4
- [Sum_range] is C4:C23
Press “Enter”. The cells are now linked with the list.
You can change the list reference to check the link between the cells.
In this method, we will make a hyperlink drop-down list. Let’s learn!
First, make two new worksheets and name them “Hardware” and “Software”. In the “Hardware” sheet, the data is
And for the “Software” sheet,
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”.
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.
Name this “E3” cell “Worksheet_Link” and click “OK”
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”.
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 reference cell, Insert this fromula,
- [ref_text] is ADDRESS(1,1,,,E3)
Now the “ADDRESS” function is
- 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.
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.
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”.
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.