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.
Download Practice Workbook
Download this practice sheet to practice while you are reading this article.
5 Unique Ways to Link a Cell Value with a Drop Down List
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 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.
- 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:
- Reference is $B$5
- Rows and Columns is 0
- [height] is COUNTIF($B$5:$B$100,”<>”)
- 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.
- 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 Dynamic Dependent Drop Down List 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.
- 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:
- Lookup_Value is E5 (The drop-down list)
- Table_Array is B5:C22
- Col_Index_Num is 2
- We want the exact value (FALSE)
- 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.
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:
- Range is B4:B22
- Criterion is E5
- [Sum_range] is C4:C23
Read more: How to Make a Drop-Down List Based on Formula in Excel
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”.
- 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,
- [ref_text] is ADDRESS(4,5,,,E4)
Now the ADDRESS function is
- Row_num is 4 for the linked worksheet.
- Col_Num is 5 for the linked worksheet
- [abs_num], [a1] is ignored
- [sheet_text] is E4.
- 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”.
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 thought regarding this article please let us know in the comment section.
- Create Drop Down List in Multiple Columns in Excel (3 Ways)
- How to Make Multiple Selection from Drop Down List in Excel
- Excel Drop Down List Depending on Selection
- Conditional Drop Down List in Excel (Create, Sort and Use)
- How to Edit Drop-Down List in Excel (4 Basic Approaches)
- VLOOKUP with Drop Down List in Excel
“Source Validates to an error” every time. Whether I use my own data and change the numbers, or match this perfectly. Not sure what I’m doing wrong when I copy directly, but I’ve followed a dozen different tutorials (At least), used existing sheets, creating sheets from scratch, you name it. Same error every time.
Dear MIKE WELLS,
Thanks for your response.
There are several reasons behind this “Source Validates to an error” occurring. You can solve them efficiently providing the correct data inside the string.
Reason: The source validates that an error can occur if the source to which the formula is applied demonstrates an error. Thus the final statement stands to whether the formula is not correct or the formula refers to data returning an error.
Solve: To solve this problem, you have to provide correct data or change the formula.
From our dataset, suppose we are having trouble with the “Source Validates to an error“. This error is occurring due to the blank cell (E4). As the INDIRECT function converts a string into an actual reference thus finding the reference cell (E4) blank it’s showing an error. Check the following screenshot.
In order to solve this just put the same text from the list in the reference cell and you won’t find any more errors further.
Other reasons behind this “Source Validates to an error” might happen-
a) The applied formula is not the proper formula to find the references.
b) Put exact names from the list to the reference list to avoid errors.
Hope you will find your solution with this reply. If you are still having problems, don’t hesitate to let us know below. We are always here with you to solve your problems. Thanks!