Forum Discussion
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
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
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)),"")
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)),"")- Thanks Sergei. Works like a charm. You're a lifesaver.
BestYou are welcome
SergeiBaklan Hello, is there a way to do this on excel teams? Excel online. Im running into this issue of not having a name manager. THanks!
That's not necessary. Instead of named formulae you may create helper range(s) at any place of the worksheet like
and reference data validation list as
- 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.
