PowerQuery can achieve this.
You will need to modify you original file however to help set things up.
Open your file up. Highlight your current data including columns. Hit Ctrl+T to convert to a table.
When you select a cell on the table you will see a new menu at the top appear (Table Design). Here you can change the default table name to something meaningful e.g. tblData. You will need to know the name for later on.
With any cell selected on the table go to Data>From Table/Range and a new window will appear.
Select Advanced Editor to bring up the code window.
In there replace everything with the code below. Note you will need to edit it to match your table name if not tblData.
Code:
Click Done.
On the right hand side should be a 'Name' box. Change to something like tblOutput.
Close the window and save changes. After a short moment you will have a new sheet with a table showing your data.
All you need to do now each time the main table is updated is to right click on tblOutput and choose Refresh which should pull in any new data.
Bookmarks