First: How to Find Many Matches
To get to our dependent data validation list, we need to be able to extract all records for each category (in the example, all apps belonging to each division). My approach is to use the INDEX together with AGGREGATE function in order to come up with a CSE (Control Shift Enter) free version of the formula.
Second: Absolute Column Table Referencing
The second challenge is to fix the column referencing in the table so that it’s fixed and doesn’t move as we drag the formula.
Third: Create a Drop-down List that Excludes Empty Values
Here I use OFFSET together with COUNTIF and OFFSET again, to get a drop-down list that is restricted to the non-empty cells. I will be making a separate video on this in the coming months to explain the technique used in more detail.
AND finally in the end we get to our dependent drop down list 🙂
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
