Compiling data from several excel files into a main file

New Contributor

Hello community,

I am trying to find the most automated way to solve the following problem:


I receive a variable number of response forms filled in by different people, in the shape of excel files with an identical structure. I want to retrieve some predefined variables from these files and use them in a main excel document, to make averages and statistics.

 

I would like to minimise the number of actions requiered to link a new response form to the main statisticsfile when I receive one (so avoid having to link every files manually). Ideally, there would be a directory containing all the excel response forms, and the main excel would go through all the files in that specific directory.


For example, imagine a directory containing X excel files with a number written in cell A1, plus a main file that averages all those numbers from the X files. If I add or remove a file to/from the directory, the average is updated.

 

What strategy would you use to solve this problem?

 

Thanks for the help! Loïc

3 Replies

@loiccharriere If you had not tagged your post with "Excel on Mac", I would have suggested Power Query. Any chance you can get your hands on a PC with Excel?

@Riny_van_EekelenI work on PC, but the person I'm helping and will be using these files is on macOS.

I see that Mac supports Power Query data refresh, but not authoring. Maybe I could set it up and then she could use it fine on Mac ?

@loiccharriere Not if you want to connect to a folder, as far as I know.