Drop Down Validation Double Dependency

[gqtrades]

Hello I am fairly new to excel and visual basic and was wondering if maybe anyone could help.

I created dependent dropdown validations using the built in excel features and also using the indirect function. It works the way it should but now if you select the Door Mount dropdown list you will see that there is right and left mounting options. When you select the Motor Mount dropdown list when FS is selected in cell E8, you find the options L1 through R5 where L is for Left and R is for Right. I wanted to know if there was anyway I could make it so when the end user selects the left or right door mount options it automatically affects the Left or Right options for the Motor Mount.

Simply I would like to create a function that says if E8 ="FS" and H2="Left..." then display motor mount dropdown options L1 - L5. Sorry I know this might be petty but for some reason I cant get around this and I find myself driven crazy if I cant figure something out.

P.S. This could have been solved with an easy dependent drop down list but I find the issue to be paradoxical since the cell I want to refer from already has another dependency , maybe there is a way around it?

Thank you very much for your time.

[Bob Phillips]

Setup two named ranges for the left, FSL, and the right, FSR, values, and use

=INDIRECT(E8&LEFT($H$2,1))

[gqtrades]

It works and that is genius, thank you very much. Anyway you can tell me why or how it works?

[Bob Phillips]

Much the same way as the original works, but here you just concatenate the first letterof H2 onto that selected value, thereby getting FSL or FSR which you indirect into.

[gqtrades]

Hmm..ok..but if thats the case then how come I cant substitute different variables(other options)..this might work for left and right but what if my choices started with different words? and what if I had more than 2 criteria? could I just separate by another &...Im trying to make sense of it but it seems that it would only work for this situation..am I right?

[gqtrades]

do you think this might be achieved somehow?

[Aussiebear]

Then construct a new Indirect

[Bob Phillips]

Hmm..ok..but if thats the case then how come I cant substitute different variables(other options)..this might work for left and right but what if my choices started with different words? and what if I had more than 2 criteria? could I just separate by another &...Im trying to make sense of it but it seems that it would only work for this situation..am I right?

Just create another named range. For instance, if you could have Middel *, then create a range FSM.

[gqtrades]

Thanks alot guys, I think Im getting this...so for example if I am trying to get the motor type in cell E8 and I already created the named ranges on the side of the sheet accordingly and I want the dependency to be based on both cell D2 and D8 and none of the options selected have similar names, based on what I think I understand this should work?

=INDIRECT(D2&MOTOR($D$8,1))

If so, how would I concatenate a third dependency?

[gqtrades]

I actually figured out the first part and realized where I went wrong and it had to do with naming the lists incorrectly, however how would I be able to concatenate a third dependency (cell) within an indirect?

[Bob Phillips]

You've totally lost me here. What do you think Motor($D$8,1) will do? and why are you concatenating D2, that points to its own set of ranges?

[gqtrades]

Yeah I made a huge mistake and named the ranges wrong and didnt understand correctly how the indirect function worked but now I think I have it and here is the revised workbook --- If I need to concatenate 3 cell references instead of two within the indirect function, how would I go about doing so?

[Bob Phillips]

E8 has the formula =INDIRECT(D2&LEFT($D$8,1)), which would give something like CFSP, and there is no such named range - the DV evaluates to an error. What 3 conditions are you trying to combine, and have you setup the named ranges required?

[gqtrades]

That is true it would give me CFSP ors CFSH but what if I have two things that start with H but are unrelated and effect my validation? I havent set up the ranges but hypothetically speaking what would be my setup?

[Bob Phillips]

I really don't know what you mean. Hypothetically might work for you, its your spreadsheet, it does nothing for me.

Simply put, if you have 3 criteria, you use

=INDIRECT(cond1&cond2&cond3)

[gqtrades]

Here is my example :

If D2 is CFS
and D8 is Motor
and E8 is FS

Then H12 is Fire Alarm and I12 are the Fire Alarm Options

[Bob Phillips]

I am not trying to be awkward, but what is that telling me? I see no range named CFSMotorFS, and where do H12 and I12 come into it?

[gqtrades]

I think you are right I am stressing way too much over this, Thank you for your help I really do appreciate it. If you could just answer one more question for me that would be great...

Since CFSM corresponds to CFS Motor and CFSH corresponds to CFS Handcrank then how I do I make another range that would correspond to CFS Handchain

In other words...You see they both start with H, how do I differentiate them so that Excel understands which named range I am referring to?

[Bob Phillips]

That's a tricky one. I would have a lookup table, maybe like so

Item Id
Handcrank Hc
Handchain Hx

and do a VLOOKUP in the DV, and of course assuming the named ranges use Hc and Hx.

[gqtrades]

Im going to try to give it a shot and hope it works for the best, Thanks so much mate.

[Aussiebear]

Since the selection options seem to be nearly endless, what about filling a form strictly from dropdowns? This means that the user has to make a series of choices until it becomes clear enough to have the system decide to remaining options.