Datavalidation dropdown based on multiple criteria

[Cboggie]

I have 3 dropdowns.

Codes, Groups, Materials

I have a table with data and I am trying to base the 3rd dropdown list on the selections of the first two.

See attached for clarification.

thanks!

[Naveed Raza]

Hi Check it Out the attached file

[:) Sixthsense :)]

D14 DV Custom Formula

=OFFSET(Table!$A$1,MATCH($D$12,Table!$E:$E,0)-1,5,LOOKUP(2,1/((Table!$A$1:$A$2823=D10)*(Table!$E$1:$E$2823=D12)),ROW(Table!$E$1:$E$2823))-MATCH($D$12,Table!$E:$E,0)+1)

[Cboggie]

D14 DV Custom Formula

=OFFSET(Table!$A$1,MATCH($D$12,Table!$E:$E,0)-1,5,LOOKUP(2,1/((Table!$A$1:$A$2823=D10)*(Table!$E$1:$E$2823=D12)),ROW(Table!$E$1:$E$2823))-MATCH($D$12,Table!$E:$E,0)+1)

Sixthsense, your code seems to match only drop down 2. For example, select 1ABA and Filter Media....you should only get 4 results. I really like your code because its one line.

Thank you Naveed +rep

[Cboggie]

Sixth,

It actually shows the 4 its supposed to, but includes others...

dropdown.png

[:) Sixthsense :)]

It actually shows the 4 its supposed to, but includes others...

At present I am not in my system, will give you the solution by tomorrow since currently in 2007 version of excel.

[Cboggie]

Ok,

Naveed's suggestion works great. Would it change the approach if I needed to have multiple sets of dropdowns accessing this same data? With using data validation lists, i will have to duplicate lots of data. These 3 drop-downs will be duplicated several times on the same sheet so users can choose multiple chemicals.

[:) Sixthsense :)]

Sixth, It actually shows the 4 its supposed to, but includes others...

Please check ROWS 83 to 86 data.

You should keep the sorted data to get the actual list.

[Cboggie]

Please check ROWS 83 to 86 data.

You should keep the sorted data to get the actual list.

Yes, rows 83 to 86 is the only data the dropdown should show if I choose 1ABA and Filter Media, however, it shows much more.

[:) Sixthsense :)]

Yes, rows 83 to 86 is the only data the dropdown should show if I choose 1ABA and Filter Media, however, it shows much more.

Then what about Row 12 to 15 Data?

Please keep unique data

[Cboggie]

Then what about Row 12 to 15 Data?

Please keep unique data

Row 12 to 15 is for 1AAG

83 to 86 is for 1ABA

[:) Sixthsense :)]

In E14 Cell

=SUMPRODUCT(--MATCH(1,(Table!$E$1:$E$2823=$D$12)*(Table!$A$1:$A$2823=$D$10),0)-1)


In D14 Cell Validation Formula


=OFFSET(Table!$A$1,E14,5,LOOKUP(2,1/((Table!$A$1:$A$2823=D10)*(Table!$E$1:$E$2823=D12)),ROW(Table!$E$1:$E$2823))-E14+1)

Refer the attached file

[Cboggie]

Refer the attached file

Awesome Sixth.....do you know why it brings in an extra?

extradropdown.png

[:) Sixthsense :)]

Awesome Sixth.....do you know why it brings in an extra?

Just remove the +1 from the suggested formula

=OFFSET(Table!$A$1,E14,5,LOOKUP(2,1/((Table!$A$1:$A$2823=D10)*(Table!$E$1:$E$2823=D12)),ROW(Table!$E$1:$E$2823))-E14+1)

[Cboggie]

Just remove the +1 from the suggested formula

I promise I tried that without luck. But today, it works

Again, appreciate your help.