Excel Dynamic Arrays (How they will change EVERYTHING!)

Excel Dynamic Arrays (How they will change EVERYTHING!)

 

 

Excel Dynamic Array formulas are new to Excel Office 365. Don't miss out on their full potential. Complex tasks will be done with simple Excel formulas. Everything you wished Excel could do, can now be done!  

 

In this video, I take two of my popular videos which use traditional Excel complex formulas and use Dynamic array formulas instead. I also use a timer to test the time needed for the new formulas and compare it to the other videos. Here's what happens: We create a dynamic dependent drop-down list based on a messy data set. 

 

The first list includes unique values from cells and the second dependent data validation list filters the values to the ones that belong to the category selected from the first list. I also make sure the list is dynamic so if we add more data to the table, the drop-down list will automatically update (here we use the new dynamic array spill range reference #)  I also use the sort function to get sorted names in the drop-down list.  We use 3 Excel dynamic array functions: Unique, Sort & filter.

Related Topics:

• Excel VBA FIND Function (and how to handle if value NOT found)
• Excel VBA Arrays: Practical Example of a 2 dimensional array to create a New Workbook 
• Excel VBA: Loop through cells inside the used range (For Each Collection Loop)
• Excel VBA IF THEN Statement (with ELSEIF and Looping in cells)