Dynamic Data Validation List or ComboBox

I'm lost on a certain aspect of VBA. On my sheet [Main Screen], which is a custom cookbook, I have an activex combobox list corresponding with category and a data validation list corresponding with total time, and those are set up fine.

I'm trying to find a way to make a data validation list, or ActiveX control box in $N$2 next to the recipe header. This list will look at the [Recipe Database] page and list each recipe title that falls into the selected category AND total time (I have a value of ANY to potentially return all titles).

Is there a way to do this? All the other data on the page is pulled from the VLOOKUP function looking at the recipe title, and I can create workarounds to display it if I need to change comboboxes to data validation lists, or vice versa.

Here is an example with formulas and data validation. It will be much more powerful with VBA but I do not have time to write the whole code for you.

Thanks for this, I'm currently trying to deconstruct the code to see exactly what you did but more importantly, how!

So I'm at the part of the code where you create the LARGE function. I understand the function prior to that and that everything after and including the COUNTIFS function is trying to point to the kth value of the LARGE function. I just don't understand how the array is constructed

I know the function works as it's supposed to, but I can't understand exactly how. If you don't mind clarifying this one part, I'd appreciate it

Originally Posted by iMrTidy

Here is an example with formulas and data validation. It will be much more powerful with VBA but I do not have time to write the whole code for you.

Ok, so I figured out the code, and can even replicate it, which is great. I'm still trying to figure out how to include the secondary category as well.

Can I change the range somewhere in the code to account for the Secondary Category column, as well as change how the countifs function works?

Hello,

Briefly, I created some support data in 'Lists'. For the first, to get what 'Times' is chosen I used formula in G1. Then I defined each time in numbers (min and max G1:H6) since 'Recipe Database - Total time' is a number. Then I applied combination of formulas to get items from 'Recipe Database - Receipe Name' one by one. The core part is SUMPRODUCT which takes a range and limits it by given conditions and return only those row's numbers which satisfy all the conditions. LARGE and COUNTIFS are used to find where to start since those rows which do not satisfy result in zero. The rest should be easy to undertand.