not just a dependency issue

Hello everyone,

ive literally watched about 20 different guides on dependent drop down lists, but they are all just simple "2 way" lists.. what I mean by that is that
they typically look at a list and select options based on one identifier..

What im trying to achieve:

I am trying to write a way for someone to use a spreadsheet to specify product options, without having to keep refering to books and options, that have mutliple dependencies..

Specifics:

I have 4 product material types (that might grow to 5)
The product material options are:
Softwood, Hardwood , Plastic, and Alumium

They can all be used in the manufacture of a specific part, which can be one of 5 fixed widths.. (the primary choice of options after the product material has been chosen).
The 5 widths are: .47, 64,76,89, and 114mm
BUT, Aluminium cannot be used ifor the narrowest width...so can only work with 64,76,89 and 114
After product width has been selected, there are 4
options for a control mechanism that will connect the item above..
Centrally controlled, control on rear, control on side, or hidden control parts...
BUT !!! Plastic cannot have HIDDEN CONTROL... Nor can ALUMINIUM !!, both regardess of the width.. so Central, offset, or side control only
to complcate things..
the 47mm louvre an not have hidden controls, regardless of material !! but the other 3 are mechanisms are OK... but dont forget you cant have 47 on plastic or Aluminium !!!
1 have 2 more dependent options
style
and colour,

and there are further dependencies based on material choice, that effects BOTH those those options,

ive tried using lists, and x-lookups, but it feels like im working witha a 6 dimension rubiks cube !!
its just frying my brain on the logic...

is there a specific function anyone can recommend I go and look into ?

many thanks in advance...

Pies

Thanks Glenn

product type updated : its MS365 subscription....

File attatched... ive included the list dependencies, and a pic of the user form i started paying with...

kind regards
Pies

Hopefully a more readable worksheet..

basically you shouldnt be able to select an "illegal" option....

and once the options are set i will then work out a price based on the options..

Cheers

ah... theres a second dependency on controls/material

grrr.. sorry ignore previous

Hi Glenn,

in an ideal world it wouldnt matter where the user started ... because in the real world somoene might have something specific in their head ..
eg: " i absolutely dont want to see a control mechanism"... ergo, the waterproof and aluminium options are automatically taken out of the "next" step, as is the 47mm louvre...

I thought about channeling them down the "product" route first, or "style", and it was just a simple case of the experience I have with this is that its 50:50..

My thinking was that they could literally be presented with the userform , or something like, that I put in the first spreadsheet.. obviously with more options.., and they could literally click ANY option, which would then scrape the lists (as per last spreadsheet) to create the next pool of options that they HAVENT yet selected...and the userform would then dynamically "hide" radio buttons or options, or whatever/however, such that they get funnelled correctly into a decision...

that way, if a new material or option comes along, the system would simply look at options "not yet" selected and present, (based on ALLOWED CRITERIA) the next groups of criteria...

or am I overthinking...?

The "26 colours", "8 stains" ..etc would ideally be expandedin the future so that they can actually see the detailed list of colours & stains..

Im really looking to design a fluid system that just consists of simple tables, with dependency requirements that can sometimes be dependent on 2 or more OTHER
criteria.. I wa also thinking down the nested loop route but the logic beat me stupid !!

cheers

so... made a little progress, but not variable from "any starting point"... in other words you are funnelled a specific route...

would love this in a whizzy user form with options disappearing etc based on ANY secific first choice...

The attached sheet supercedes others

would be nice if options reset each time as opposed to having to click into the "data validated" cell...
also, if you make several choices and the decide to change the top option (style) let say to SOLID, then all the other options should clear...

cheers]