• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dependent drop down combo box. (see attached file)

N

ninad7

Member
In the file I have the values for the combo box for sheet "6a" stored in the worksheet "Weightages" in a range named "Scores" (cells C29:C49). Based on the cell value E73 in worksheet 6a, I want to control the list values in the drop down as seen in rows 97 and 101 only.


For e.g. if E73 is 30, then the drop down list for combo boxes in rows 97 and 101 should only contain values upto a max of 30 (i.e. 0,5,10,15,20,25,30).


For e.g. if E73 is 20, then the drop down list for combo boxes in rows 97 and 101 should only contain values upto a max of 30 (i.e. 0,5,10,15,20).


Can someone help in achieving this or propose an alternate solution.


TIA.


Regards,


Ninad.
 
No attachements.... I believe you cannot attach a file on this blog. or maybe Chandoo has not activated the feature.


You can upload the file on a public share like Skydrive etc. and put the link here..


if you want please feel free to mail it to me at sharma.vijay1-at-gmail.com


~Vijay
 
Ninad,


on the sheet 6a, I have create a new dynamic named range called as testList.


=OFFSET(Scores,0,0,MATCH(E73,Scores,0),1)


and then assigned this to the Drop Down list property.


sending the file to you to have a look


cheers


~Vijay
 
Houston We have a problem...


The dynamic named range refreshes when assigned to a simple cell validation list using the Named range...


However the (ActiveX) combobox list fill range does not refresh unless the workbook is saved ... closed and open again...


Researching....


~Vijay
 
Okay... here are the finding...


1. Tried using Data Validation... works OK

2. Tried using Data Validation with Dynamic Range assigned to the list.... work OK

3. Tried using Form Controls with Dynamic Range..... work OK

4. Tried using ActiveX control with Dynamic Range ..... NOT OK


Ninad,


I am emailing you the sample file as well to have a look... possibly you could use the Form Controls in your beautifully designed workbook.


~Vijay
 
Hi Vijay,


Thanks. Will change the drop down to Forms Control. If you do get the answer to ActiveX refresh, please do enlighten me as well.


Have a great day.


Ninad.
 
Solution found for the Combox Box refresh as well


In the sheet put any dummy formula such as MAX(E73); we know it will always have single value only.


Then in the sheet code put the below

rename the sheets as per the requirement


Private Sub Worksheet_Calculate()

Sheets("Sheet1").ComboBox1.ListFillRange = "testList"

End Sub


Now everytime E73 is updated; because we have the formula which needs to be calculated. the Combobox gets refreshed.


Strange but works like a charm. So you can stick to the ActiveX combobox.


HTH


~Vijay
 
I have a list (Validation) in "A2" and it has ABC and BCD as drop down. Now "C2" should actually show another list depending on the selection of ABC or BCD. Please help
 
Hi Tinu Mathews,


Here is the solution with full explanation for your question :)


http://chandoo.org/wp/2008/11/25/advanced-data-validation-techniques-in-excel-spreadcheats/


Regards,

Prasad
 
Hi I am facing problems in here. i have uploaded the file in hotfile link - http://hotfile.com/dl/139854571/4da7eab/Error_Classification.xlsx.html


i am able to get the drop down in the 2nd Level but the list is blank. Please help again
 
Hi ,


Change the Data Validation formula for the second drop down list to :


=OFFSET(F2,MATCH($G$2,$E$2:$E$96,0)-1,0,COUNTIF(E2:E96,$G$2),1)


Narayan
 
You Know Narayan .... You are just awesome. i have been going through few of your posts and was always satisfied with the solutions that you give. Just one more question what is the significance of "-1" in this formula -


=OFFSET(F2,MATCH($G$2,$E$2:$E$96,0)-1,0,COUNTIF(E2:E96,$G$2),1)
 
Hi ,


Thanks for the appreciation.


The syntax of the OFFSET function is :


=OFFSET(reference,rows,cols,height,width)


reference is the point with reference to which you wish to offset ; in this validation list , the list for the second drop down list starts from F2 , and continues till F96.


rows is the number of rows that should be offset from reference ; an offset of 0 means F2 , an offset of 1 means F3 and so on. In this case , you are using a formula MATCH($G$2,$E$2:$E$96,0) , which returns the number in the array E2 through E96 , where it finds a match for G2. If the very first entry i.e. cell E2 matches G2 , then the MATCH function will return 1. However , if the very first entry matches , we would like to start from F2 itself ; this can only be done by subtracting 1 from the result of the MATCH function ; hence the -1.


So basically the MATCH function return value starts from 1 , whereas the OFFSET function starting point was F2 , and using the MATCH return value as is , would have meant we would start from F3 ; you can , if you wish , use the following validation formula :


=OFFSET(F1,MATCH($G$2,$E$2:$E$96,0),0,COUNTIF(E2:E96,$G$2),1)


It all sounds very confusing ! Hope your question is answered.


Narayan
 
Never confusing Narayan, that was well explained. As I said have been reading most of your posts and all are well explained. I need your assistance for most of my cases. I would be posting more. You are one of those guys who explain very well. There is one more a person called Hui i feel, if i am not wrong.


And thanks once again for the explanation.
 
Hello Team,

I have the same requirement. I need to insert three combo boxes into my sheet. The entries of the second combo box is only restricted to the selection of the first combo box entries. For example if I select state A in combo box 1, categories only for state 1 is being shown in the second combo box. I read what it was described on the top, but it is hard to understand and follow. Can we take care of this task without VBA code?

Your help is greatly appreciated.

GGGGG
 
Hi ,


Can you go through the following link contents , and see if you can do what is required on your own ?


http://www.davesexcel.com/dependentcombobox.htm


Narayan
 
Hi Narayan,


I will plug the code in, but I can't figure out to get the code to work if the code doesn't work. Is there any way to take care of this task without code? Thank you for the help.


Regards,

Guity
 
Hi, GGGGG!


There's a similar topic where I posted some time ago. Here's the link:

http://chandoo.org/forums/topic/data-validation-list-automatically-shows-the-first-item-in-the-list#post-21858


Hope it's what you're looking for.


Regards!
 
You must log in or register to reply here.
Back
Top