Forum Discussion

bengal1022's avatar
bengal1022
Copper Contributor
Apr 30, 2020

Drop Down List with Linked Columns

Hello:

 

I am trying to figure out how to create a drop down list with multiple linked columns; but let me explain as it is different from what I am seeing on the forum and other sites. 

 

I have a worksheet in the data base with vendor contact information. It has 4 columns:

Vendor Name

Contact Name

Email

Phone Number

 

I have another worksheet with Purchase Information, with the same four columns. I want to know how I can create a drop down list in this worksheet that will allow one to select the "Vendor name" from a drop down list, and once it is selected then the other 3 columns would populate with the respective information from the vendor contact worksheet. 

 

I have attached the spreadsheet as reference.

 

Is this possible? And if so, how would I go about it?

 

Thanks

 

  • bengal1022 

    I'd create two named ranges

    one for data validation list

    ='Vendor Contacts'!$A$2:INDEX('Vendor Contacts'!$A:$A,COUNTA('Vendor Contacts'!$A:$A))

    and another for vendor info range

    ='Vendor Contacts'!$B$2:INDEX('Vendor Contacts'!$D:$D,COUNTA('Vendor Contacts'!$A:$A))

    using first in data validation and second to pickup the info as

    =IFNA(INDEX(VendorsRange,MATCH($L2,VendorsList,0),MATCH(M$1,'Vendor Contacts'!$B$1:$D$1,0)),"")
  • bengal1022 

    I'd create two named ranges

    one for data validation list

    ='Vendor Contacts'!$A$2:INDEX('Vendor Contacts'!$A:$A,COUNTA('Vendor Contacts'!$A:$A))

    and another for vendor info range

    ='Vendor Contacts'!$B$2:INDEX('Vendor Contacts'!$D:$D,COUNTA('Vendor Contacts'!$A:$A))

    using first in data validation and second to pickup the info as

    =IFNA(INDEX(VendorsRange,MATCH($L2,VendorsList,0),MATCH(M$1,'Vendor Contacts'!$B$1:$D$1,0)),"")
  • Lewis-H's avatar
    Lewis-H
    Iron Contributor
    Select the cell where you want the first (main) drop down list.
    Go to Data –> Data Validation. ...
    In the data validation dialog box, within the settings tab, select List.
    In Source field, specify the range that contains the items that are to be shown in the first drop down list.
    Click OK.

Resources