Conditional Data Validation Drop-down Lists

Hi all,

I am cross-posting here; here is the link to my original post: http://www.mrexcel.com/forum/showthr...ighlight=Gos-C

I have a complex task that I am trying to accomplish, and need your help.

Sheet1 is my database and looks like the following:

HTML Code: 

Sheet2 is for the user input, in which Columns H and I would have a data validation drop-down list and looks like the following:

HTML Code: 

I want the drop-down list in Column H to show all the unique items from Column H of Sheet1 - sorted in ascending order.

For the drop-down list in Column I, when the user selects an item from the drop-down list in Column H, if Column AP of Sheet2 in not blank, I want the drop-down list to show all the items from Column G of Sheet1 that where Column H of Sheet1 matching the selection. If Column AP of Sheet2 is blank, I want the drop-down list to show only the items from Column G of Sheet1 where Column H of Sheet1 match the selection and also have Column K of Sheet1 showing INP. For example:

if Bird is selected and Column AP of Sheet2 is not blank, the drop-down list would show Chicken, Feather, Fly, Nest and Turkey - sorted in ascending order; but

if Bird is selected and Column AP of Sheet2 is blank, the drop-down list would show Chicken, Feather and Turkey - sorted in ascending order.

HTML Code: 

Records are continuously being added to Sheet1, and Column A contains formula to generate the RefNo.

Thanks for your time and help,
Gos-C

Hi all,

I have figured out the unique subset list (see attachment), but am still unable to come up with the dependent list. I would appreciate any help.

Thank you,
Gos-C

Why not just use AutoFilter on Sheet1? Make a selection in col H, and then in col G.

Have a look at the attached...

I created a separate list that lists all the corresponding items to your 1st drop down choice... this list is in column N of Sheet1.

N1 gets the item you picked in your first drop down..

Formula in N2, copied down gets all the items in your original set that correspond to that choice. This is an array formula and must be confirmed with CTRL+SHIFT+ENTER keys

I created a dynamic named range called "FinalSubset". This collects all the items in column N of Sheet1.

Then the data Validation list formula to get that 2nd set is =FinalSubSet.

Hope that helps.

Great! Thanks a million, NBVC. Now I just need an alternative list to satisfy the final condition:

HTML Code: 

Then I would use an IF function to determine which lists to show.

NBVC, I appreciate your help.

Gos-C

So you mean if Sheet2!AP1 is occupied, then you want your whole list from column G to appear in the drop down and if Sheet2!AP1 is not occupied, then only show list from column G where column H matches the 1st drop down choice in Sheet1?

If so, then replace the formula in N2 of Sheet1 with this formula:

Please Login or Register  to view this content.

and confirm with CTRL+SHIFT+ENTER and copy it down the column enough to get all entries when AP1 is occupied.

As far as having it in sorted order, why don't you just presort the list in column G....

Otherwise, VBA would be the better solution.

Hi NBVC,

I will respond late.

Thank you,
Gos-C

Hi NBVC,

Regarding your question:

PHP Code: 

So you mean if Sheet2!AP1 is occupied, then you want your whole list from column G to appear in the drop down and if Sheet2!AP1 is not occupied, then only show list from column G where column H matches the 1st drop down choice in Sheet1? 


No. If Sheet2!AP1 is occupied, then show list from column G where column H matches the first drop-down choice from Sheet1. Your first post has already accomplished that.

However, I want a second list: If Sheet2!AP1 is not occupied, then show the list from column G where column H matches the first drop-down choice and also has INP in column L.

Gos-C

See attached.

I added another list in Column Q to list all the INP results when AP1 is blank.

This new list is named FinalSubSet2.

And there is a new drop down in in Sheet2!J2 to show this list when AP1 is blank.

Hope that's what you wanted.

Hi NBVC,

You are the best! Thank you, thank you, thank you! You have captured the concept.

I should have mentioned that the cell in column AP (Sheet2) is relative to the row where the choices from the drop-down lists are being selected. For example, if the drop-down lists are in Row 10, then it's AP10.

Sorry for taking so much of your time.

Gos-C

Not really sure if this has been resolved or not...

..but in case it isn't...

What I would suggest is the following:

Copy all columns N:R from Sheet1 to adjacent columns the same number of times as you have dropdown rows in Sheet2. (If you are not using my attached sheet, you will need to first make some of the reference relative instead of absolute.. ie. you will need to delete some $ symbols). See formulas in N:R in attached.

You will have to change the Countif() formulas you copy from P1 to reference the cell your 1st drop down is in for each set.

Then Go to Insert|Name|Define and create new names for FinalSubset and FinalSubset2 each of the copied columns. Note: You may want to rename those first ones too and give them a little more descriptive names.

For each of those new named ranges, you will have to revise the formulas to reference the corresponding columns.

Then go to Sheet2 and for each drop down, update the corresponding List formulas to match the appropriate columns.

Hope this helps!

If this is resolved, please mark your Post as [SOLVED] per instructions when you first login

Hi NBVC,

Thanks again for your help. I will be working on the sheet today, and then I will let you know if I was successful.

Have a great day.

Gos-C

P.S. BTW, I am in Toronto (and work in Mississauga).

Hi NBVC,

I am still struggling with this, and wonder:

Can't some VBA code be used to change Sheet1 N1 to the current drop-down selection on Sheet2?

For example (though it needs improvement):

Please Login or Register  to view this content.

Please advise,

Thank you,
Gos-C

You should use target when using event macros

Please Login or Register  to view this content.

VBA Noob

Originally Posted by Gos-C

Hi NBVC,

I am still struggling with this, and wonder:

Can't some VBA code be used to change Sheet1 N1 to the current drop-down selection on Sheet2?

..


Please advise,

Thank you,
Gos-C

In what way are you struggling? Can you post a new sample showing your problem? I suppose anything you can do in Excel you can do with VBA (sometimes more effectively and efficiently)... but I am not too much of a VBA guy to help you out efficiently... so if you want VBA solution, try posting in the Programming forum and specifying that you want a VBA solution and perhaps post a link to this thread for history purposes.