Drop Down Populates Another Cell in Excel & Google Sheets

This tutorial demonstrates how selecting an item from a drop-down list can populate a different cell in Excel and Google Sheets.

 

 

VLOOKUP to Populate Another Cell

Create a drop-down list using data validation in Excel.

Now, use the VLOOKUP Function in a different cell to look up the value you select from the drop-down box.

Select the cell where you wish the VLOOKUP formula to go, and then type in the following formula:

=VLOOKUP(F2,B3:C9,2,FALSE)

The drop-down value is held in cell F2 while the table (database) values are held in the range B3:C9. Match the value in F2 with the correct value in the range B3:B9, and then return the corresponding Total Orders value.

 

 

Select a different Salesperson in the drop-down list, and the figure returned in F2 will change accordingly.

 

 

VBA to Populate Another Cell

You can also use VBA to populate another cell in Excel by using a change event when the value in a drop-down box is amended. You would first need to create a drop-down box through the Developer Ribbon instead of data validation. If you don’t see the Developer tab, you’ll need to enable it.

In the Ribbon, select Developer > Insert > ActiveX Controls, and then select the drop-down control.

 

 

You can then drag and drop to create a drop-down control in your worksheet. Click Properties in the Developer tab to switch on the properties for the drop down. Select the source for the drop-down list and the linked cell where the list will go.

 

 

You can then use the ComboBox_Change event to create some VBA code that will run when the value of the combo box is changed.

Drop Down to Populate Another Cell in Google Sheets

Google Sheets is also able to use the VLOOKUP Function to change the value in another cell based on the selection in a drop down list.