Dynamically change the named range for data validation list

[sheter]

Working to create a drop-down to dynamically change the selectable data set.

QUESTION 1: (In the attached file) if "GOTHAM (ICO)" is selected, how can the Wattage drop-down be populated with the correct data?

QUESTION 2: (In the attached file) if a new row is added to the "Wattage" data set, how can the named range dynamically include it?

Thanks!

 

[bosco_yip]

Try,

In H3 >> Data Validation >>

Allow : List

Source :

=OFFSET(INDEX(B3:B100,MATCH(H2&"*",B3:B100,0)),0,MATCH(G3,C3:E3,0),COUNTIF(B3:B100,H2&"*"))

>> OK

Regards
Bosco

 

[sheter]

oh wow! that's exactly it!!!!

Thank you!

 

[bosco_yip]

In H3 >> Data Validation >>
Allow : List
Source :=OFFSET(INDEX(B3:B100,MATCH(H2&"*",B3:B100,0)),0,MATCH(G3,C3:E3,0),COUNTIF(B3:B100,H2&"*"))
>> OK
Regards
Bosco

Hi,

Just remove the Index function, which is not required.

and, become >>

=OFFSET(B3,MATCH(H2&"*",B3:B100,0)-1,MATCH(G3,C3:E3,0),COUNTIF(B3:B100,H2&"*"))

Regards
Bosco