This post will guide you how to create a drop down list and how to auto fill cells in Excel based on a dependent drop down list. When selecting a value from the drop down list, then it should be able to populate the value in another corresponding cell.
For examples, you have created a drop down list based on the values in range B2:B4, and when you select any value from the drop down list, the corresponding value in range c2:C4 should be populated in another cell automatically.
Auto Populate Cells Based on Dropdown Box Selection
#1 select one cell that will be holding the drop down list. Such as: B1
#2 Go to DATA tab, click Data Validation command under Data Tools group.
#3 the Data Validation window will appear.
#4 change the Allow: value to “List” from the drop-down list under Validation criteria section. Then select range B2:B4 as Source data.
#5 the drop down list has been created.
#6 type the below formula in another cell D6, press Enter key.
#7 select another value “Word” from the drop-down list in Cell B6, you will see that the corresponding value in Cell D6 will be populated automatically.