VBA data validation, dependent dropdown list

Hi everyone,

I'm working on an excel that consists of a fill-in form within the worksheet. I understand it may be better to use a userform and activeXcontrols, but I already have 2 userform in the workbook.
*The workbook needs to be usable by all users, therefore I can't use plug-ins.
*I'm totally new in VBA, so please avoid "refer to this link" only. I won't be able to understand the code unless it has comments. I need to know where and which part I can/cannot change.

I need to generate a dependent dropdown list. Which I managed to have it working, however, (as attached) I need to place the reference value in Cell M2. Is there possibly a method to not having to display a value in cell M2 and still have it working?

Notes:
1. information of column [F, G] and [I,J,K] is not constant, as user can add new "company", "product", "detail" to list
2. the actual lists is separated into different worksheet (ie. sheet1 = in-sheet form, sheet2 = company list, sheet 3 = product list)
3. reason that I'm doing this in VBA and not excel built-in functions is to avoid formula loss (overwriting the data without realizing it and then saving the workbook [which happened before])

Possible Error:
I realized that if the list from column I to K is not sorted according to column I, the drop down list will generate mismatch

For example
Col I Col J
A apple
A orange
B Beef
A Lemon

with this list above, the dependent drop down list for A will be (apple, orange, BEEF). It can count there are 3 "A" but the results are shifted.

I appreciate for your help!

Please Login or Register  to view this content.

Hi,

It looks like you are wanting to learn Cascading Validation Lists. Read about them at:
http://www.excel-user.com/2011/02/ca...ion-lists.html

Because you want to add to data it also seems you need more than Named Ranges. There are these magic things called Dynamic Named Ranges that may help in your puzzle.
Read about them at:
http://www.bettersolutions.com/excel...G820716330.htm

I hope the above topics will help with your problem. Note - No VBA required for either of the above topics, as you requested.

Originally Posted by revzephyr

... I need to place the reference value in Cell M2. Is there possibly a method to not having to display a value in cell M2 and still have it working?

If you don't use cell M2, how would the user select a company?

Edit: disregard. I'm a little slow tonight.

Try this.

For the B2 Data validation list formula use this.
=INDIRECT("DetailA")

And this for C2
=INDIRECT("DetailB")

This will use two named ranges as their list ranges


The code below will update the DetailA and DetailB named ranges when you make a change to A2.

You could also put the lists on other sheets and edit the code to those sheet names and ranges. When the form-sheet is activated, the named ranges are updated to reflect any changes the user may have made.

Please Login or Register  to view this content.

"company" isn't a company; It's a header. There is no detail data associated with it.

Change the DV list in A2 so it doesn't have "company" in the list. Problem solved.