Excel Conditional Drop-Down Lists Using Dynamic Arrays - Episode 2248

Microsoft Excel Tutorial: How to set up Dependent Validation in Excel Using New Dynamic Arrays. This is also known as Conditional Drop-Down Lists or Cascading Data Validation.

Welcome to another episode of Learn Excel From MrExcel! In today's podcast, we will be discussing how to use dynamic arrays for dependent validation. This topic has been addressed twice before on the podcast, but with the new dynamic arrays feature announced in September 2018, it has become much easier to implement. However, please note that this feature is currently only available for Office 365 users and is still being rolled out gradually.

To begin, we have two formulas - the UNIQUE formula and the SORT formula. The UNIQUE formula returns all the classifications and the SORT formula sorts them in alphabetical order. This gives us one formula that returns five results, which we will place in cell D4. Next, we will use this formula as the source for our data validation in cell E4. The # symbol ensures that all the results from D4 are returned, even if we add a new category in the future.

Now, let's move on to the more complex part - filtering the list in column A based on the chosen category in column B. We will use the FILTER formula for this, with the criteria being the chosen category in cell H3. However, we also need to account for the scenario where no category has been chosen yet. For this, we will use the third argument in the FILTER formula, which will display "Choose Class First" if no category is selected. This ensures that the user is prompted to choose a category before the list is filtered.

If you're not familiar with dynamic arrays, I highly recommend checking out my book "Excel Dynamic Arrays", which is available for free download until the end of 2018. It includes this example and 29 others to help you understand and utilize this feature to its full potential.

In conclusion, dynamic arrays have made dependent validation much easier to implement in Excel. If you're not on Office 365 and don't have access to this feature yet, you can refer to our previous podcast (episode 1606) for the old method. Thank you for tuning in, and don't forget to check out our other netcasts for more Excel tips and tricks. See you next time!

#excel
#microsoft
#exceltricks
#excelhacks
#microsoft365
#excelnew
#evergreen

This video answers these common search terms:
excel cascading dropdowns
conditional drop-down list excel
how to do conditional data validation in excel
how to make conditional data validation excel
how to create conditional data validations in excel
dynamic validation lists in excel
how to make dynamic data validation excel
how to have a dynamic data validation list excel
what is data validation in excel and how to use it
how to create two data validation lists in excel
how to find data validation on excel
how to do an offset data validation in excel
excel how do you do data validation
how to create data validation drop down in excel
excel dependent drop-down list
dependent data validation excel
linked dropdowns in excel
excel dropdown based on another cell

Table of Contents:
(00:00) Explanation of Dependent Validation Using Arrays
(00:24) Introduction to Dynamic Arrays
(00:51) Two Formulas for Dependent Validation
(01:04) Setting Up Data Validation
(01:22) Explanation of First Validation Formula
(01:32) Explanation of Second Validation Formula
(01:43) Importance of Choosing a Category First
(02:03) Use of "Choose Class First" Argument
(02:27) Explanation of Data Validation
(02:43) Clicking Like really helps the algorithm

Can you set up Excel Data Validation so that the choices in a second drop-down menu are dependent on the choice in the first menu? For example, if you choose Bagels, the answers might be Raisin, Tomato, or Everything. If you choose Pancakes, then the answers would be Buckwheat, Blueberry, or Pumpkin.
This has been solved twice before on my channel using Names or using OFFSET. Today, with the introduction of new Dynamic Array formulas, there could be an easier way.

[excelisfun]

Awesome! Array formulas are not hard - they are easy : ) Thanks for the great video, Mr Excel : )

[LeilaGharani]

Many thanks Bill for the great video! We almost released at the same time :)

[SergioAlejandroCampos]

Great Bill. The world will change with these dynamic arrays. Thanks.

[mazarata]

Thank you MrExcel - was just watching Leila on the same.... great tutorial too - but she was faster ;-)

[sktneer]

Great video Bill! The actual fun starts now with those dynamic array formulas. :)

[excelymasoficial]

Thank you very much Bill for your high quality content :)

[bharatkakad]

Great video. Any suggestions for resources/training that would describe appropriate approaches/best practices for a Data analyst. Often times articles/videos will talk about introducing various concepts but the efficient selection of the concepts for a problem type is not talked about (understandably so, due to the varied nature of how different people use excel), which also determines how successful one is with solving a problem in the least amount of time. Thanks in advance.

[wayneedmondson]

Hi Mr. Excel. Thanks for the excellent intro to solving the dependent validation issue with the new dynamic array functions.. and I was just getting the hang of doing it the old way.. haha!! Can't wait for the general release of these new functions. Looking forward to how you and the other EXCEL MVPs will use them in new and creative ways.. Thumbs up!

[itamimii]

Thanks Bill. Short video but too valuable.

[erictsang]

For the Filter function, can we choose the field to be filtered>
For example, i want to filter the field, A1 and A3, instead of =filter (a1-a4, , how can i select only 1a and a3 to be filter?/


Eric

[CloudStudio]

Hi. I want to create 2 drop-down lists wherein the 2nd drop-down list is dependent from the first one.

Now, the data for my 2nd drop-down list are results from a formula which include cells whose resulting values are "".

The problem I'm facing is when I created my 2nd drop-down list. It include those cells with "" values which I don't want to happen.

Any solution for this?

[ahmedal-dossary]

Thanks Bill, first time to see # trick and this new array

[mattschoular]

Thanks MrExcel… I can't wait for this rollout....

[learnspreadsheets]

This is good but it’s really only a one cell solution. I had hoped you could choose in one col step 1, the 2nd col choose the subcategory and then more columns for sub-sub categories! Etc. If only we could type into the data val box =filter...

[dawidca]

I have an idea for a video for you MrExcel. I am curious if it is possible to create a user formula in vba that spills. I know that user formulas were able to change only the value of the cell they were entered in. Has it changed with the new dynamic arrays?

[PedroCabraldaCamara]

such a cool trick for the source in list validation

[smartcfo]

Hi there :-)
Is it possible to use Dynamic Array formulas to create a cascading validation list? We do a lot of accounting system migrations where accounts in the old accounting system need to be mapped to a new chart of accounts. So what we are trying to do is to take the list of the old accounts and as we select say for instance Income Statement then all the Income Statement groups (Revenue, Cost of Sales, OPEX, etc) are filtered in the validation list. When I select for instance Cost of Sales then the validation list dynamically updates with the Cost of Sales categories (Direct Labour, Direct Material, Finished Goods, etc).

The following video demonstrates the principle of what we are trying to achieve. Except we have to cascade 4 or 5 Levels into the account structure.


For Example: Old Account (5000 - Technical Staff Salaries - Permanent)
- Level 1: Report Type -> Income Statement
- Level 2: Report Group -> Expenses
- Level 3: Report Category -> Cost of sales
- Level 4: Account Group -> Direct Labour
- Level 5: Account Category -> Direct Labour - Permanent
- Level 6: New Account Name -> COS: Labour cost (Permanent)

The above mapping needs to happen in a cascading manner based on the selection of the previous level.

Hope it makes sense?