Data Validation Lists - Separate dropdowns created from table

Hello All,

I am trying to help out a friend who works in manufacturing. The model being manufactured contains 5 parts that can have different choices of materials for each. My friend needs a dropdown for each part so he can choose which material to use for that part. Some of these materials can be used on multiple parts (refer cells A1:I6 in attached WB) for e.g. Material H can be used on parts #3-#5 but not on parts #1 or #2.

I need to create 5 separate data validation lists - 1 unique dropdown for each part - using the table (cells A1:I6) in the attached WB. I can't think of a way to create these dropdowns using formulas so that they only include the materials marked TRUE for that particular part (e.g. dropdown for Part #5 should only allow materials C & H. dropdown for Part 3 should only allow E, F & H).

Can anyone please help me out? Thanks.

With this layout, you can enter as many values in column A that you wish, the column H drop downs will expand themselves.

Then put the appropriate number of entries in B:C so the data validation in column I can lookup the range of rows on its own.

Great. Thank you! I'll try out over the weekend and let you know how it goes.

You actually may find this layout easier on the eye. On the Lists sheet, just add as many lists as you wish. Row1 is used to dynamically create the first drop down called "Parts", then the second drop down is dynamically created in the pink DV formula cells.

Impressive. +1

I will probably end up using this however I was hoping to have the dropdown lists in a table-like layout as in my original WB (post #1). If I deleted the "FALSE" entries, would it be possible to set up named ranges (using ranges B2:I2, B3:I3 etc) that ignore the empty cells in these ranges? I guess I would need to change the TRUE entries to

Formula:

Please Login or Register  to view this content.

Nope. Your table would require a bit of deep VBA and enabled macros. My solution is VBA-free. No charge.

I'm not sure that it is impossible without resorting to VBA. I have a (unfounded ) hunch that with some fancy OFFSET and LOOKUP formulas in the Named Range formula area this might be pulled off.

Attached is closer to what I had in mind.

What I would have liked to do is have the Named Ranges run straight off the data in Range G1:L9 (with the use of various functions in the Named Range formulas to remove the empty cells). However I don't know how to do this. So I have to rely on the use of formulas in ranges A1:E9 and N2:T9 which looks messy and takes up space.

I am closing this thread however if anyone does know of a way to use Named Ranges to ignore empty cells in a range, please send me a PM. Thanks.