Excel file in sharepoint - how to auto update or auto export when data is populated

So once more forms have been filled in/updated etc. you want to go straight to File C to pick up changes but you use File B as a middle man to get the form data first?
If so then C will never be able to update unless you update B and save.

Not sure what exactly you are doing with File B before its goes to File C but can't you just skip File B (or copy the elements of this into C) and get the form data pulled straight into C?

Best way to pull data from another file would be to use PowerQuery - depends on what you are doing exactly I guess. PowerQuery allows you to pull data from a file, or even from a folder if you have several files with same structure, and populate in your current workbook (File C) as a table. You can then manipulate the source data as needed. Once there are more forms or data changes (A) you simply refresh the data in File C. Don't need to open the source files - just need them to be save with data changes of course.

So I have attached 2 files to show you how PowerQuery can make things quick for you. It might look complicated but once you've got your head around it you will realise it's not to much work and the ease of updating is well worth it.

First though you need to set things up for them to work:

1) create a folder somewhere, on your desktop for example. Just to contain these files and you can quickly delete later. I will call it Folder01 for the below.
2) in this folder save the File B.
3) Create a folder called Data in Folder01.
4) With Example01 open it and change the data on the first sheet. Save it in the Data folder.
5) Repeat step 4 so you have several files.

Right so hopefully you already understand that the Data folder contains the File A's as per your original post. File B of course is File B.
Open File B. There is a table with one row of data. Right click on it and choose refresh. After a short moment the table will magically show each File A you created with data entered on each.

At this point you need to take the to to understand that there is a table on File A which is used to create one 'big' table to pull into File B. If that makes sense and you can see the benefits read on for a basic how to.
This how to guide is a basic start from scratch and you will need to apply to your actual work. File C will be mentioned at the end.


1) Assuming the File A is to remain unchanged create a new sheet which pulls the information you need into a row. Give each column a heading And then convert to an Excel table (Select the area and hit Ctrl+T)
2) Save the file. Create a folder where you what to keep the files (when you go live with this all files in this folder need to be the same format - keeping old audits here will result in errors)
3) Create you File B which will pull the data form your audit files.
4) In file B select Data>Get Data>From File>From Folder. Might be slightly different with different versions of Excel.
5) with the pop up choose the folder location where your new File A's are saved.
6) you should see your files listed, choose Combine>Combine and Load.
7) A new sheet will appear with a table showing all your File A's.

Now delete one or more File A's
ON File B right click on the table and hit refresh. Those files will disappear. Add some File A's and refresh the table. They will appear.
Once the setup is done it's as simple as that to refresh.

File C. You can instead use Data>Get Data>From File>From Workbook on this one. Just find File B and then select the sheet or Table which has your data.
Note you may want to choose Transform instead of Load which will allow you to modify the File B data before it is loaded into File C (doesn't physically change File B).

I will leave it at that for now to get your head around all this. YouTube and general searches on PowerQuery for Excel will give you the basic starter guides which will help compliment the above and make things clearer.