Compiling data from several excel files into a main file

%3CLINGO-SUB%20id%3D%22lingo-sub-2358890%22%20slang%3D%22en-US%22%3ECompiling%20data%20from%20several%20excel%20files%20into%20a%20main%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2358890%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20community%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20find%20the%20most%20automated%20way%20to%20solve%20the%20following%20problem%3A%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20receive%20a%20%3CSTRONG%3Evariable%3C%2FSTRONG%3E%20number%20of%20response%20forms%20filled%20in%20by%20different%20people%2C%20in%20the%20shape%20of%20excel%20files%20with%20an%20%3CSTRONG%3Eidentical%20structure%3C%2FSTRONG%3E.%20I%20want%20to%20%3CSTRONG%3Eretrieve%20some%20predefined%20variables%3C%2FSTRONG%3E%20from%20these%20files%20and%20use%20them%20in%20a%20main%20excel%20document%2C%20to%20make%20averages%20and%20statistics.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20minimise%20the%20number%20of%20actions%20requiered%20to%20link%20a%20new%20response%20form%20to%20the%20main%20statisticsfile%20when%20I%20receive%20one%20(so%20avoid%20having%20to%20link%20every%20files%20manually).%20Ideally%2C%20there%20would%20be%20a%20directory%20containing%20all%20the%20excel%20response%20forms%2C%20and%20the%20main%20excel%20would%20go%20through%20all%20the%20files%20in%20that%20specific%20directory.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CEM%3EFor%20example%3C%2FEM%3E%2C%20imagine%20a%20directory%20containing%20X%20excel%20files%20with%20a%20number%20written%20in%20cell%20A1%2C%20plus%20a%20main%20file%20that%20averages%20all%20those%20numbers%20from%20the%20X%20files.%20If%20I%20add%20or%20remove%20a%20file%20to%2Ffrom%20the%20directory%2C%20the%20average%20is%20updated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20strategy%20would%20you%20use%20to%20solve%20this%20problem%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20help!%20Lo%C3%AFc%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2358890%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2358985%22%20slang%3D%22en-US%22%3ERe%3A%20Compiling%20data%20from%20several%20excel%20files%20into%20a%20main%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2358985%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1055073%22%20target%3D%22_blank%22%3E%40loiccharriere%3C%2FA%3E%26nbsp%3BIf%20you%20had%20not%20tagged%20your%20post%20with%20%22Excel%20on%20Mac%22%2C%20I%20would%20have%20suggested%20Power%20Query.%20Any%20chance%20you%20can%20get%20your%20hands%20on%20a%20PC%20with%20Excel%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2359006%22%20slang%3D%22en-US%22%3ERe%3A%20Compiling%20data%20from%20several%20excel%20files%20into%20a%20main%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2359006%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3EI%20work%20on%20PC%2C%20but%20the%20person%20I'm%20helping%20and%20will%20be%20using%20these%20files%20is%20on%20macOS.%3C%2FP%3E%3CP%3EI%20see%20that%20Mac%20supports%20Power%20Query%20data%20refresh%2C%20but%20not%20authoring.%20Maybe%20I%20could%20set%20it%20up%20and%20then%20she%20could%20use%20it%20fine%20on%20Mac%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.