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

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset to Link a Cell Value with an Excel Drop Down List

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.

Data Validation to Link a Cell Value with an Excel Drop Down List

  • 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:

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

Where,

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

Data Validation dialog box to Link a Cell Value with an Excel Drop Down List

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

Link a Cell Value with an Excel 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.

Read More: Create Excel Drop Down List from Table (5 Examples)


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.

How to Link a Cell Value with an Excel Drop Down 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”.

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

  • 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


Similar Readings


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.

How to Link a Cell Value with an Excel Drop Down List

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

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

Where,

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

Link a Cell Value with an Excel Drop Down List using VLOOKUP function

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

Read More: How to Create List from Range in Excel (4 Easy Ways)


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:

=SUMIF(B5:B22,E5,C5:C22)

Where,

  • Range is B4:B22
  • Criterion is E5
  • [Sum_range] is C4:C23

Read More: Excel Formula Based on Drop-Down List (6 Suitable Examples)


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

Link a Cell Value with an Excel Drop Down List

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

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

how to link a cell value with a an Excel drop down list

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

=INDIRECT(ADDRESS(4,5,,,E4))

Where,

  • [ref_text] is ADDRESS(4,5,,,E4)

Now the ADDRESS function is

=ADDRESS(4,5,,,E4)

Where,

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

how to link a cell value with a an Excel drop down list

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

how to link a cell value with a an Excel drop down list

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


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 thought regarding this article please let us know in the comment section.


Related Articles

Asikul Himel
Asikul Himel

Hi! I am Asikul Islam Himel. Glad you are here. I am a Team Leader of ExcelDemy, running an excellent team of five efficient Excel & VBA Content Developers. Here at ExcelDemy, we give the best sustainable solutions by posting articles related to MS Excel-related problems. I have completed my under graduation degree from Bangladesh University of Engineering and Technology and my program was Naval Architecture and Marine Engineering. I have found passion in data analysis and research-based fields. I am currently working to grow my leadership quality. I have a great interest in project management and critical thinking. In my free time, I love to travel and read books.

2 Comments
  1. “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!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo