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


100 Excel Simulations

100 Excel Simulations »


Covering a variety of Excel simulations, from gambling to genetics, this introduction is for people interested in modeling future events, without the cost of an expensive textbook.

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.

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.

Power Pivot and Power BI

Power Pivot and Power BI »


Power Pivot for Excel and its close cousin Power BI Desktop are Microsoft’s tightly-related pair of revolutionary analytical tools – tools that are fundamentally changing the way organizations work with data.

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.

Pivot Table Data Crunching: Microsoft Excel 2016

Pivot Table Data Crunching: Microsoft Excel 2016 »


Use Excel 2016 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours… understand exactly what’s going on in your business… take control, and stay in control! Even if you’ve never created a pivot table before, this book will help you leverage all their amazing flexibility and analytical power.

see more products »