I need to create a worksheet list with more than 1000 BRAND and BRAND MODEL(BUT I ONLY SHOW 3 SAMPLES). The Drop Down function for the requirement as below:
for example:
when I point to the BRAND LAVAL and all the related LAVAL MODEL ONLY will reflected in the Dropdown list (not included other Brand model).
---------------------------------------------------------------------------------------------
I can do it manually each specific BRAND and BRAND MODEL drop down list with specific range but there are more than 1k plus brands. This could take ages to complete. Possible there is a formula to cater this down down list I'm looking for. Thank you
for example:
when I point to the BRAND LAVAL and all the related LAVAL MODEL ONLY will reflected in the Dropdown list (not included other Brand model).
The Reason for this requirement when the USER add/point to the specific name like LAVAL. The Brand Model of the LAVAL only reflected in the drop down list. This can prevent USER add/Point to the wrong Brand Model.
You will need helper columns which you can hide later. First, create unique list of brands with UNIQUE
in G2
:
=UNIQUE(A2:A10)
In D2
set data validation to list pointing to source G2#
Second, create dynamic array for models with =IFERROR(FILTER(B2:B10,A2:A10=D2),"")
in H2
In E3
set data validation to list with source H2#
Result:
Hide columns G
and H
if needed.