MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dependent Validation Using Arrays


October 18, 2018 - by Bill Jelen

Dependent Validation Using Arrays

Ever since Data Validation drop-down menus were added to Excel in 1997, people have been trying to work out a way to have the second drop-down menu change based on the selection in the first drop-down.

For example, if you choose Fruit in A2, the drop-down in A4 would offer Apple, Banana, Cherry. But if you choose Herbs from A2, the list in A4 would offer Anise, Basil, Cinnamon. There have been many solutions over the years. I've covered it at least twice in the MrExcel Podcast:

With the release of the new Dynamic Array formulas in Public Preview, the new FILTER function will give us another way to do Dependent Validation.

Say that this is your database of products:

Build validation based on this database
Build validation based on this database

Use a formula of =SORT(UNIQUE(B4:B23)) in D4 to get a unique list of the classifications. This is a brand new type of formula. One formula in D4 returns many answers that will spill into many cells. To refer to the Spiller Range, you would use =D4# instead of =D4.

continue reading »

More Reading


Can You Return All VLOOKUP Values?

Can You Return All VLOOKUP Values? »

October 16, 2018 - by Bill Jelen


VLOOKUP is a powerful function. But I often get a question in one of my Power Excel seminars from someone who wants to know if VLOOKUP can return all of matching values. As you know, the VLOOKUP with False as the fourth argument will always return the first match that it finds. In the following screenshot, cell F2 returns 3623 because it is the first match found for job J1199.

A Look Back At 13 Years of The MrExcel Podcast

A Look Back At 13 Years of The MrExcel Podcast »

October 12, 2018 - by Bill Jelen


In the summer of 2005. I was flying to Toronto every month to record two appearances on Leo Laporte’s Call for Help TV Show. On the way home, I was having dinner at the airport bar and the guy sitting next to me said a sentence that was a complete mystery to me: “Did you know that Leo’s TWiT Podcast is the Number One Podcast on iTunes?”

read more articles »


IMA Data Analytics Leveraging Excel

Featured Products


Excel Dynamic Arrays Straight to the Point

Excel Dynamic Arrays Straight to the Point »


The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.

Supercharge Excel When You Learn to Write DAX For Power Pivot

Supercharge Excel When You Learn to Write DAX For Power Pivot »


Hands-on book to learn and master the DAX language!

Learn to Write DAX

Learn to Write DAX »


Data analysis expressions (DAX) is the formula language of PowerPivot and Power BI. Simply reading a book is normally not enough to help Excel users learn DAX skills – you need to complete exercises and get plenty of practice to make the transition.

Excel JavaScript UDFs Straight to the Point

Excel JavaScript UDFs Straight to the Point »


JavaScript custom functions - UDFs can be used like any other native functions or UDFs in Excel. This book shows the process of creating JavaScript UDFs in Excel Developer Preview for Office Insider program subscribers.

M is for (DATA) MONKEY

M is for (DATA) MONKEY »


Despite the moniker "data monkey", we information workers are often more like data magicians. Our data seldom enters our world in a ready-to-consume format; it can take hours of cleaning, filtering, and reshaping to get things ready to go. Power Query will make this process faster the first time and reduce it to a single button click every subsequent time.

Your Excel Survival Kit

Your Excel Survival Kit »


This book helps you realize that Excel is on your side. It can be your friend, not your enemy. If and when you upgrade your Excel skills you will experience many positives: you will learn to love Excel, you will learn how to use Excel to become an invaluable asset in your workplace.

see more products »