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


EXCEL UNPLUGGED

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…

View original post 322 more words