Apr 30 2020 02:18 AM
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
Apr 30 2020 02:48 AM
SolutionI'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)),"")
Apr 30 2020 03:21 AM
Apr 30 2020 05:41 AM
Apr 30 2020 02:48 AM
SolutionI'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)),"")