Excel Unplugged

Creating a dynamic Dropdown List with Data Validation from another workbook with a little help from Power Query

Probably the longest title of all times, but it leads to something great. First let’s explain Power Query. Power Query is an AddInn for Excel and part of Microsoft’s Power BI (get it here). It is proving to be a tool Excel Users have been (unknowingly) waiting on for a very long time. This is a very simple use of that tool to achieve something that has so far been next to impossible to achieve.

The trick here is how to dynamically connect two Workbooks. So when you get new data in one, the other Workbook should result this growth (or shrinking) of data. We start this by having a Workbook called SourceDynamicRange.xlsx. On a Sheet called Data, a table called MyTable resides. It is very important that this is an Excel Table!

Creating a dynamic Dropdown List with Data Validation from another workbook with a little help from Power Query

Now we go to another Workbook called DestinationDynamicRange.xlsx and there we go to Power Query tab and choose From File and From Excel.

Creating a dynamic Dropdown List with Data Validation from another workbook with a little help from Power Query

As we point to our SourceDynamicRange.xlsx as an Excel file we wish to import, a list of all Sheets, Tables and Named Ranges (static names, not dynamic) is given. We choose MyTable. Also Notice a little preview of the selected data source which can be very useful in larger files.

Creating a dynamic Dropdown List with Data Validation from another workbook with a little help from Power Query

As we choose to import MyTable we get a new query and a table that is an exact replica of MyTable but resides in the DestinationDynamicRange.xlsx workbook.

Creating a dynamic Dropdown List with Data Validation from another workbook with a little help from Power Query

The best thing about it is, if we now choose to add data to our original table (the one called MyTable that resides in the SourceDynamicRange.xlsx). In this case I’m adding the next four months.

Creating a dynamic Dropdown List with Data Validation from another workbook with a little help from Power Query

Save that file and go to the DestinationDynamicRange.xlsx, right click the replica table and choose Refresh (letting the query run again)…

Creating a dynamic Dropdown List with Data Validation from another workbook with a little help from Power Query

There it is, eternal happiness.

Creating a dynamic Dropdown List with Data Validation from another workbook with a little help from Power Query

Now that this data is stored in this workbook, you can create a dynamic Named range. In this sample, you go to FORMULAS/Name Manager

Creating a dynamic Dropdown List with Data Validation from another workbook with a little help from Power Query

Choose to create a new Named Range in my case I chose DynamicRange as a Name and in the Refers To you write

=OFFSET(Sheet9!$A$2,0,0,COUNTA(Sheet9!$A:$A)-1,1)

Creating a dynamic Dropdown List with Data Validation from another workbook with a little help from Power Query

If you are interested in the OFFSET sunction and how the dynamic range written above works, read this article.

Now select the cell where you want your Data Validation dropdown and go to DATA/Data Validation

Creating a dynamic Dropdown List with Data Validation from another workbook with a little help from Power Query

Choose List in the Allow dropdown and in the Source write “=” and the name of your dynamic range. In my case that is =DynamicRange

Creating a dynamic Dropdown List with Data Validation from another workbook with a little help from Power Query

And there it is

Creating a dynamic Dropdown List with Data Validation from another workbook with a little help from Power Query

A Data Validation dropdown that is connected to a table in this workbook which is a dynamic replica of MyTable range in another workbook. Pure brilliance!

If you wish to read more about different (and more sofisticated) uses of Power Query, here are two articles you might find interesting:

Using Unpivot Column with Power Query

The new Pivot Column option in Power Query

Comments 9

  1. Daniel says:

    Interesting
    However you can achieve the same results without using a dynamic range name, just select the Months column in the file DestinationDynamicRange.xlsx and give it a name. Because the source of this name is an Excel table column it will be a dynamic range name.
    Faster and more robust.

  2. John says:

    Hi Gašper Kamenšek,

    Thanks a lot for this useful article at “Drop list by using power query”, i have used this to link more than 10 files and making Lists, My question is:
    When am using a source ( From file – from Excel ) can i select one or two columns only from the sourced file not all columns from the sheet?
    Because i have a huge data and dependency with a heavy size, slowly opening files!

    Thanks,
    John,
    Oracle Consultant,

    1. Sure you can, when you are in the edit Query mode, just select the two columns you wish to work with, right click and select Remove Other Columns.

  3. Pubudu says:

    This is very nice. Thank you for letting me know….!

  4. Chelsea Avery says:

    Hello. Do all users need to have the power query addin enabled in order for this to work?

    1. If they want to update the query then yes.. But just to view the results, you don’t need Power Query.

  5. Dave says:

    Is there a way to have the new table in your DestinationDynamicRange.xlsx to update automatically when you open the spreadsheet?

    1. You can check your Power Query update settings in the Properties option of the Data/Connections dialog box and there you will find a Update on file open checkbox…

Leave a Reply

Your email address will not be published. Required fields are marked *

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