Dependent Drop Down lists reset

[allheilyb1]

Hey guys,

I'm new here on the forum, so nice to be part of the family and I hope you guys will be able to help me out! Here is the topic:

I created a spreadsheet in which I've got 5 drop down lists, all dependent to each other. So depending on what I put in cell J2, solutions are going to pop up in cell K2, then solutions in cell L2 are dependent from J2&K2, etc... until N2 (see attached). Everything has been fine until now (I still have to rename the all matrix in tab 2 though...), but I got stuck when it comes to make this bad boy work a more efficient way.

In fact, what I would like my spreadsheet to do is to simply reset values in cells K2, L2, M2 and N2 to the first value in the drop down list when I change the value in J2. The point is that then, I can sort my data so the most common value I need is first in the drop down list and appears as soon as I change the value in J2.

Now I read that thread

http://www.excelforum.com/excel-prog...ult-value.html

which is sort of what I need. But I do not know how to adapt the code to my spreadsheet....

Thanks in advance

[daffodil11]

Not sure what the other post said, but I went with:

Please Login or Register  to view this content.

I'm pretty VB naive but I can franken-code reasonably well. This code goes directly on the VB project worksheet of Lighting. It activates any time a cell in J2:M499 is changed, and then it blanks out the cell to the right. If that cell isn't in Column M, it cycles around again until it does, effectively blanking out the rest of the row up to M.

Edit: I read that post just for kicks. ChemistB is a brilliant man. My version is just using a variable targeted range.

[allheilyb1]

Great! thanks for the reply

Although your answer gets me closer to the solution, it doesn't fix my problem. What I'm trying to do is not to have to select values for columns K, l, M, and N. And have a default value -the first one in each drop down list- every time I select something in the column J.

Hope this is clear for y'all.

[daffodil11]

Ooooo. Perhaps theres a way to pull the first value of the named ranges as default.

My logic is running towards: WorksheetFunction.Choose(1,Array("named range"))

[daffodil11]

Took a while to teach myself the answer, but I finally did it. (Best way to learn, by the way)

Please Login or Register  to view this content.

This works for a single column.

Now let me finish applying it to the rest.

[daffodil11]

Almost, just two more cases:

Please Login or Register  to view this content.

[daffodil11]

Going into this I had no clue what Case even was. Glad I got that down now.

Here's the finished version:

Please Login or Register  to view this content.

[allheilyb1]

Wow... This is working almost perfectly. Really helped out here!!!!

Thank you a lot

[daffodil11]

No problem. Always fun to learn new things.

[allheilyb1]

Hey I'm back! haha

I might need your help here again for a little modification... I don't know if it is fixable though.

I've got some drop down lists where I have no data cause well, it does not apply to this specific categories. So let's say I'm picking "Inc" for the lamp type, there is no length and ballast data for it. So excel put a 0 for the length and it kinda screw up the automation (see spreadsheet). Is it possible to adapt the code and make it work so it returns a blank cell instead of a 0?

thanks in advance

[daffodil11]

Sure is! I barely know what I'm doing, but this seems to work:

Please Login or Register  to view this content.

There's a second procedure after the formula is entered to check for an error. If it creates one, it just blanks the cell out.

[allheilyb1]

@daffodil11

Sir I think I got one more problem. My last column (N - "Ballast Option") does not behave the same way as the previous do and updates o #REF! instead of the first choice of my drop down lists. This is I believe only one more line in the code, but I don't want to mess it up.

[daffodil11]

Can you show me a combination of the choices that creates an error?

The workbook you attached did not have the additional code from post #11 in it.

I've added it into this book.

[allheilyb1]

Well, when I pick my value in drop down list column J "lamp type", the colomn N "Ballast Option" don't update to thee first value available as column K, L & M do. And also you can see the description doesn't match the type of light I actually pick until I pick the ballast option -I do not know why, I can't figure it out!