Linking a data sheet to a KPI sheet

Here is how I do it. Establish a directory where you want the data to go. In my case, I have a directory specifically used for data downloads. Then download the file to this directory using the same file name in each case. Overwrite this file every time. Then Use Data > Get Data > Form File > From Workbook. Then navigate to the workbook, and follow the prompts for download. The tool is fairly intuitive. You can do things like remove columns and do filtering.

What's better is that the data comes into an Excel Table. This means that every time you refresh data, it replaces the old data with the new. Furthermore, if you have helper columns with formulas, they are applied automatically and copied down automatically. Also, since it is a table, all pivot tables and charts built off them refer to exactly the right amount of data. You will have to refresh the pivot table after you download the data.

If you got to Data > Queries & Connections, right click on the query and select properties, and check off the box that allows background query. Then you can update everything with Data > Refresh All.

The attached word document contains an explanation of how to do it with screen captures. I also included the sample database and the resulting report. It will NOT work for you unless you have the same directory structure as I do. What I suggest you do is follow the instructions in the word document and create the report on your own.

If you still have questions, respond to this post and attach the file you are trying the read, and the file you are trying to read into. Mock up the results you want if you have to.