• 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 Data Validation - with Index & Match

Kenny9002

New Member
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.
 

Attachments

  • SAMPLE FILE.xlsm
    47.9 KB · Views: 15
Sorry for the delay, Kenny...it's been the weekend here, and my kids are on holidays so I haven't had a chance to look at this yet. I'll try to get it back to you tomorrow.
 
Thanks Jeff for letting me know. I am very encouraged by your promise. Very kind of you. My regards to the little ones.

Kenny
 
Have a look at this, see if it does what you need. Note I've made some changes to your other formulas.
 

Attachments

  • SAMPLE FILE.xlsm
    47 KB · Views: 31
Hi Jeff,

Thanks for your time and help. I have tested the file. Colum H Cells H24 to H40 still do not do what I need. I'll try and explain it again, may be, much better:

1. When you single-click on any of the Cells G24 to G40, you'll observe that the dropdown lists are very tiny to read.

2.When you choose from the drop-down list in that cell, the dependent lists to the item chosen will appear in the adjacent cell in column H24 to H40. With a single-click, the dependent lists in column H also appear very tiny and not easy to read.

3. However, with the help of Debra's code which is in Sheet 2 vba window, the drop-down lists can be made larger and readable by double-clicking on the cells (instead of single-click). E.g. If you single-click on Cell G24, the items in the drop-down list will appear very tiny. But if you double-click on the same G24, the items in the drop-down list will appear very large and readable. That's fine.

4. But after double-clicking and choosing from any of column G's drop-down list, if you single-click on the dependent adjacent cell (i.e. H24), the items on the drop-down list will appear tiny. You cannot double-click on any of column H cells and get a dependent drop-down list with items large and readable as the cells in G column.

5. What I'm trying to achieve is that just as you can double-click on G24 and the drop-down list fonts will be bigger (with Debra's code), after choosing an item from it, one should be able also to double-click on the dependent cell H24, and get a drop-down list to choose from - with large fonts too as in the G column cells.

6. I don't know what to do to Debra's code to enable me to achieve that for the column H cells.

7. I mentioned in my first post that your own code is right below Debra's code in the Sheet 2 vba window. I commented it out in the hope that as you try to help me, you would look at the two codes - Debra's and yours, and that you will uncomment yours and apply whatever part of it that you consider relevant to my issue.

I know that this matter has taken much of your time. I do apologize for it all, and should be very grateful for your further help with this, please.

Thanks.

Kenny
 
Hi Kenny. I'm afraid I don't have time to help you with Debra's code, or to make changes to my code. All I can help you with is the actual 2nd level data validation categories, which I've already implemented.

You may have to post a 2nd thread here in regards to getting someone to help you with that code.

Regards

Jeff
 
Back
Top