Forum Discussion

bengal1022's avatar
bengal1022
Copper Contributor
Apr 30, 2020
Solved

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...
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 30, 2020

    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)),"")
SergeiBaklan's avatar
SergeiBaklan
MVP
Apr 30, 2020

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's avatar
bengal1022
Copper Contributor
Apr 30, 2020
Thanks Sergei. Works like a charm. You're a lifesaver.

Best

Resources