Dear Experts on this Forum,
I am very happy to discover this awesome forum. And I believe I’ve come to where I’ll find a solution to my Excel problem. This thread appears long. Please pardon me. I only want to explain the issues very clearly to those that will help me.
I read a very brilliant Post by Jeff Weir titled ‘Dynamic (Cascading) Dropdowns that reset on change’. Here is the link:
http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/
I have tried to set up dependent drop-down lists, but have run into problems. I should be very grateful, please, for help. I am hoping that Jeff would read this and kindly help me. I have attached a file to make it easier for those wishing to help me.
1. My lists as shown in the PRODUCTS worksheet are from top down and I wish to retain them that way. (Jeff’s Post shows the Lists arranged from left to right).
2. The data validation are on the INVOICE sheet. The drop-downs for my MainList appear in cells G24 to G40.. And the drop-downs for my SubList appear in cells H24 to H40. I have made some progress with my MainList drop downs (G24 to G40). My main problem – where I need the most help - is with my SubList drop-downs (H24 to H40).
3. Drop-down list texts are usually very tiny and barely readable. To overcome this, I have incorporated Debra’s double-click code (Contextures) which turns the data validation cell into a combo box and makes the texts bigger and readable. Debra’s vba code is placed in the INVOICE SHEET. And it works very well for my MainList Drop-downs (G24 to G40). To get this to work, I adapted and used Jeff’s formula for the MainList as follows:
=INDEX(Table2[[Categories]],1):INDEX(Table2[[Categories]],COUNTA(Table2[[Categories]]))
I also had to add the name “=MainList” to the Data Validation source box (for the cells in G24 to H40).
4. Unfortunately, I cannot get the SubList (H24 to H40) to also work that way –i.e. respond to Debra’s code. When I double-click any of the SubList cells (H24 to H40), it doesn’t work as it does for the MainList cells (G24 to G40).
5. For this to work, I believe that I need to add the name “=SubList” to the Data Validation source box for cells H24 to H40).
6.I defined the range for the SubList which is the PRODUCT worksheet “A1 to B31” and named it SubList in the Name Manager. I adapted and applied Jeff’s formula for the SubList as follows:
=IF(OR(INVOICE!I24="Choose…",INVOICE!I24=""),"",INDEX(Table3[Category],1,MATCH(INVOICE!I24,Table3[#Headers],0)):INDEX(Table3[Category],COUNTA(INDEX(Table3[Category],,MATCH(INVOICE!I24,Table3[#Headers],0))),MATCH(INVOICE!I24,Table3[#Headers],0)))
But when I try to add the name “=SubList” to the Data Validation source box (for cells H24 to H40), I get the following error message – ‘The list source must be a delimited list, or a reference to single row or column’. At this point it does not allow me to add the name “=SubList” to the source box.
7. A Vba code also goes with Jeff’s formula. This code is also included in the same INVOICE sheet. I have merely uncommented the code in this sample file. (The experts helping out should please comment it in order to test the result).
I might have defined the range of the Sublist wrongly. Or I might have adapted the formula for SubList incorrectly. I need your kind help to resolve this and get it working for me, please.
I must also add that Debra’s code works very well for the Customer Name drop-down data validation in whichever sheet the data validation for customer names is found.
I have a very baby knowledge of vba and not very experienced in Excel formulas, and should be very grateful for your help with this problem.
With Jeff’s code, the word “Choose …” shows up on each of the data validation cells. I would like to leave this out. I would prefer the cells to be without that. I’ll be OK with just clicking on the drop-down and selecting in each case.
Thanks in anticipation of your kind help.
Kenny.
I am very happy to discover this awesome forum. And I believe I’ve come to where I’ll find a solution to my Excel problem. This thread appears long. Please pardon me. I only want to explain the issues very clearly to those that will help me.
I read a very brilliant Post by Jeff Weir titled ‘Dynamic (Cascading) Dropdowns that reset on change’. Here is the link:
http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/
I have tried to set up dependent drop-down lists, but have run into problems. I should be very grateful, please, for help. I am hoping that Jeff would read this and kindly help me. I have attached a file to make it easier for those wishing to help me.
1. My lists as shown in the PRODUCTS worksheet are from top down and I wish to retain them that way. (Jeff’s Post shows the Lists arranged from left to right).
2. The data validation are on the INVOICE sheet. The drop-downs for my MainList appear in cells G24 to G40.. And the drop-downs for my SubList appear in cells H24 to H40. I have made some progress with my MainList drop downs (G24 to G40). My main problem – where I need the most help - is with my SubList drop-downs (H24 to H40).
3. Drop-down list texts are usually very tiny and barely readable. To overcome this, I have incorporated Debra’s double-click code (Contextures) which turns the data validation cell into a combo box and makes the texts bigger and readable. Debra’s vba code is placed in the INVOICE SHEET. And it works very well for my MainList Drop-downs (G24 to G40). To get this to work, I adapted and used Jeff’s formula for the MainList as follows:
=INDEX(Table2[[Categories]],1):INDEX(Table2[[Categories]],COUNTA(Table2[[Categories]]))
I also had to add the name “=MainList” to the Data Validation source box (for the cells in G24 to H40).
4. Unfortunately, I cannot get the SubList (H24 to H40) to also work that way –i.e. respond to Debra’s code. When I double-click any of the SubList cells (H24 to H40), it doesn’t work as it does for the MainList cells (G24 to G40).
5. For this to work, I believe that I need to add the name “=SubList” to the Data Validation source box for cells H24 to H40).
6.I defined the range for the SubList which is the PRODUCT worksheet “A1 to B31” and named it SubList in the Name Manager. I adapted and applied Jeff’s formula for the SubList as follows:
=IF(OR(INVOICE!I24="Choose…",INVOICE!I24=""),"",INDEX(Table3[Category],1,MATCH(INVOICE!I24,Table3[#Headers],0)):INDEX(Table3[Category],COUNTA(INDEX(Table3[Category],,MATCH(INVOICE!I24,Table3[#Headers],0))),MATCH(INVOICE!I24,Table3[#Headers],0)))
But when I try to add the name “=SubList” to the Data Validation source box (for cells H24 to H40), I get the following error message – ‘The list source must be a delimited list, or a reference to single row or column’. At this point it does not allow me to add the name “=SubList” to the source box.
7. A Vba code also goes with Jeff’s formula. This code is also included in the same INVOICE sheet. I have merely uncommented the code in this sample file. (The experts helping out should please comment it in order to test the result).
I might have defined the range of the Sublist wrongly. Or I might have adapted the formula for SubList incorrectly. I need your kind help to resolve this and get it working for me, please.
I must also add that Debra’s code works very well for the Customer Name drop-down data validation in whichever sheet the data validation for customer names is found.
I have a very baby knowledge of vba and not very experienced in Excel formulas, and should be very grateful for your help with this problem.
With Jeff’s code, the word “Choose …” shows up on each of the data validation cells. I would like to leave this out. I would prefer the cells to be without that. I’ll be OK with just clicking on the drop-down and selecting in each case.
Thanks in anticipation of your kind help.
Kenny.