basic user

%3CLINGO-SUB%20id%3D%22lingo-sub-2198198%22%20slang%3D%22en-US%22%3Ebasic%20user%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2198198%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20have%20created%2052%20separate%20spreadsheets%20in%20Excel%2C%20is%20there%20any%20way%20I%20can%20collate%20them%20so%20i%20have%20a%20total%20for%20every%204%20weeks%20and%20an%20end%20of%20year%20one%3F%20I'm%20gonna%20guess%20not%20but%20if%20there%20is%20that%20would%20be%20fantastic.%20thanks%20in%20advance.%20Steve1965%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2198198%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2198393%22%20slang%3D%22en-US%22%3ERe%3A%20basic%20user%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2198393%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F992487%22%20target%3D%22_blank%22%3E%40Steve1965%3C%2FA%3E%26nbsp%3BPossible%3F%20Yes%2C%20but%20it%20depends%20on%20what%20you%20exactly%20mean%20by%20%22collate%22.%20Websters%20Dictionary%20offers%20two%20meanings.%20%3CSTRONG%3ECollect%20and%20combine%3C%2FSTRONG%3E%20or%20%3CSTRONG%3ECompare%20and%20analyse%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3EIf%20your%20workbooks%20are%20all%20in%20the%20same%20folder%20you%20can%20connect%20to%20all%20of%20them%20using%20Power%20Query%20and%20do%20whatever%20you%20need%20to%20do%20with%20the%20data.%20Since%20you%20labelled%20your%20message%20%22basic%20user%22%2C%20you%20may%20have%20to%20go%20through%20quite%20a%20few%20step%20learning%20the%20techniques.%20You%20may%20find%20the%20attached%20link%20useful.%20Section%209%20deals%20with%20importing%20files%20from%20a%20folder%20in%20particular.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceloffthegrid.com%2Fpower-query-introduction%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceloffthegrid.com%2Fpower-query-introduction%2F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20lay-out%2Fstructure%20is%20the%20same%20for%20all%20files%20and%20you%20would%2C%20for%20instance%2C%20want%20to%20summarise%2052%20weekly%20totals%20where%20the%20total%20is%20always%20on%20row%20250%20(or%20in%20particular%20named%20range)%20in%20each%20file%2C%20you%20can%20create%20links%20to%20each%20of%20the%20workbooks%20in%20a%20summary%20sheet.%20and%20there%20are%20probably%20more%20ways.%3C%2FP%3E%3CP%3EIf%20you%20could%20upload%20a%20workbook%20(without%20private%2Fconfidential%20information%20in%20it)%20and%20elaborate%20a%20bit%20on%20how%20the%20output%20should%20look%20like%20you%20might%20get%20a%20better%20answer.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi

I have created 52 separate spreadsheets in Excel, is there any way I can collate them so i have a total for every 4 weeks and an end of year one? I'm gonna guess not but if there is that would be fantastic. thanks in advance. Steve1965

1 Reply

@Steve1965 Possible? Yes, but it depends on what you exactly mean by "collate". Websters Dictionary offers two meanings. Collect and combine or Compare and analyse.

If your workbooks are all in the same folder you can connect to all of them using Power Query and do whatever you need to do with the data. Since you labelled your message "basic user", you may have to go through quite a few step learning the techniques. You may find the attached link useful. Section 9 deals with importing files from a folder in particular.

https://exceloffthegrid.com/power-query-introduction/ 

 

If the lay-out/structure is the same for all files and you would, for instance, want to summarise 52 weekly totals where the total is always on row 250 (or in particular named range) in each file, you can create links to each of the workbooks in a summary sheet. and there are probably more ways.

If you could upload a workbook (without private/confidential information in it) and elaborate a bit on how the output should look like you might get a better answer.