The power of Dynamic Arrays in #Excel 365

… in creating dynamic drop-down with conditions

Making a dynamic drop-down list is not difficult.  It may require multiple steps, helper formula and/or helper tables though.  And the number of helper tables depends on the complexity of the requirement.  I wrote a blogpost for a job-assignment task more than 5 years ago.  The requirement is simple: show the teacher with least hours of classes assigned to on top of a drop-down list… like the screen cast show below:

Excel tip - Dynamic dropdown with Excel 365_1

See!? The drop-down is changing according to the total hours assigned, as calculated on column F.

With dynamic arrays, this task is super easy.

You may download a Sample File to follow along.

Note: As of today, Dynamic Arrays is available to Excel for Office 365 Insider only

Step 1 – Setup the formula to calculate the total hours assigned to a teacher

In F3, input the following formula and copy down

=SUMIF($C$3:$C$28,E3,$B$3:$B$28)

Excel tip - Dynamic dropdown with Excel 365

Note: This step is required regardless of which Excel version you are using. 😉

Step 2 – Step up the dynamic array that is sorted by total hours assigned

In H3, input the following formula

=SORTBY(Table1[Teacher],Table1[Total Hrs Assigned],1)
'Note: The range E2:F8 is converted to Excel Table, named Table1

Excel tip - Dynamic dropdown with Excel 365_2

This SORTBY function is simply awesome and super intuitive.  You can read it as

Excel, please Sort the column [Teacher] by [Total Hrs Assigned] in ascending order

(1 in the third argument means ascending; -1 means descending)

Step 3 – Step up the drop-down via Data Validation

  1. Select the range where you want to have the drop-down
  2. Go to Data tab
  3. Click Data Validation
  4. Select List
  5. Input =$H$3#  (Note: The # sign is the cannot-miss magical ingredient here)
  6. OK

Note: this step is required regardless of which Excel version you are using.  The only difference is what to input in step 3.5

Excel tip - Dynamic dropdown with Excel 365 2.png

It’s all SET and DONE!

Excel tip - Dynamic dropdown with Excel 365_3

 

And the best part is… when we have new teacher added to the list, we don’t have to change any formula.  The dynamic array and Excel Table work in perfect harmony. 😁

Watch this:

Excel tip - Dynamic dropdown with Excel 365_final.gif

Isn’t it cool?

As mentioned before, Step 1 and 3 are required anyway.  What makes the huge difference is the step(s) in-between.  Before dynamic arrays, I needed four helping formula to achieve so.  With dynamic array (in Excel 365), this task becomes so simple.

What do you think?  Please leave your comments.  😉

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel in Action, Excel Tips and tagged , , , , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.