Dependent and dynamic drop-down lists

[csunseri]

Need some help with what I "think" are VBA codes. I have two lists: List 1 and List 2. List 2 is accessed in cell C15 is set up as a dependent of List 1. For example, I make a choice from a drop down in List 1 and the selections available in List 2 are affected.

List 1 (located in cell C14) contains a list of six options. If Options 1 - 4 are selected in cell C14, I want cell C15 to show "N/A." If Options 5 and 6 are chosen, I want C15 to allow two different dependent lists to be available.

Further more, I would love for cell C15 to be left blank if C14 is blank.

So I guess the logic would be something like this...
A. If C14 is blank, then C15 should also be blank.
B. If C14 is selected as Options 1 - 4, then C15 should show "N/A."
C. If C14 is selected as Option 5, then C15 should show the Option 5 dependent drop down list.
D. If C14 is selected as Option 6, then C15 should show the Option 6 dependent drop down list.

One additional note, it would be great if, when a change is made to C14, cell C15 changes per the logic in A-B no matter how many times I make a change. For example, I choose Option 4 in C14 and C15 shows "N/A." I then change C14 to Option 5 and don't want "N/A" to remain in C15. ( I hope this makes sense.)

I have seen a few codes listed in this forum for others, but when I try to copy and make the necessary reference changes, nothing happens.

Can anyone help me with this?

Thanks,
Chris

[Kyle123]

Hi csunseri,

There are loads of ways of doing this, I wouldn't opt for VBA in your situation, see the attached and see if it does what you want.

[csunseri]

Wow...this is exactly what I was describing. Only remaining question is are cells C10 and D10 combo boxes? They don't appear to be data validation lists. Also, how are I2 and I4 being updated when I make a change to the list? I don't see any formula in the cell?

Thansk,
Chris

[Kyle123]

Glad it helped

Yes, they are comboxes (forms comboboxes rather than VBA comboboxes though) they are designed to be used on your sheets without VBA. They update a linked cell with the index of the selected item (I2 & I4), the linked cell can be set by right clicking on the combobox -> Format Control -> Control -> Cell Link.

The reason that they are comboboxes rather than validation lists is that you need the default value of the dependent list to change automatically (NA when options 1-5 are selected) this isn't easy to do with validation lists (unless you use VBA). I've attached the same workbook using validation lists so you can see what I mean. - Though if you really need validation lists, let me know. Try selecting option 1 in the first list, then NA in the second - then change to option 6 in the first list, the second list will still show NA until you click to drop down

The magic is in the named range that is used to define the criteria for the dependent dropdown box, in the attached sheet I've included this so you can see how it actually works - if you have any queries, let me know