vlookup? Match? Index? Macro? im not sure

[Ctomo]

Hi Guys,

I have a spread sheet containing multiple equipment specs, i want to be able to select different specs and have a drop down list with the names of the equipment that relates to that spec, it can be 1 or 50. There are 10 different spec types, i want to be able to pick just one spec or all 10 in order to generate the name of the equipment.

Ive tried vlookup, index with a match, IFERROR with index, i cant seem to get it to work can anyone help?

Thank you

Craig

[ChemistB]

Question 1: Are some or all of the "Specs" required to be filled in. In other words, Can any of the cells in B of Breakdown be left blank? and if left blank then they could apply to any piece of equipment?

[ChemistB]

Okay, In Tools, in Column N starting at N3 copied down
=IF(SUMPRODUCT((OR(ISBLANK(Breakdown!$B$3),Breakdown!$B$3=B3))*(OR(ISBLANK(Breakdown!$B$4),Breakdown!$B$4=C3))*(OR(ISBLANK(Breakdown!$B$5),Breakdown!$B$5=D3))*(OR(ISBLANK(Breakdown!$B$6),Breakdown!$B$6=E3))*(OR(ISBLANK(Breakdown!$B$7),Breakdown!$B$7=F3))*(OR(ISBLANK(Breakdown!$B$8),Breakdown!$B$8=G3))*(OR(ISBLANK(Breakdown!$B$9),Breakdown!$B$9=H3))*(OR(ISBLANK(Breakdown!$B$10),Breakdown!$B$10=I3))*(OR(ISBLANK(Breakdown!$B$11),Breakdown!$B$11=J3))*(OR(ISBLANK(Breakdown!$B$12),Breakdown!$B$12=K3))),MAX($N$2:$N2)+1,0)
You can hide that column if you wish

In Sheet2 K3 copied down to K50
=IFERROR(INDEX(Tools!$A$3:$A$239, MATCH(ROWS($A$1:$A1),Tools!$N$3:$N$239,0)),"")

Create a Dynamic Defined name
EquipList = OFFSET(Sheet2!$K$3,,,SUMPRODUCT(--(LEN(Sheet2!$K$3:$K$50)>0)))

In Breakdown D3, Data Validation>List
=EquipList

[Glenn Kennedy]

I just got back in, to find that ChemistB has already done this. I'd taken a slightly different approach, but haven't been able to post it until now. Anyhow... Here it is....

I fixed a couple of errors in your Data Validation in "Breakdown".

The selection takes place on sheet 2, column J and a dynamic DD, based on those results, list shows your options in "Breakdown".

[Ctomo]

Guys you are both legends! Both work perfectly, thank you so much!