HELP ON REFERENCING ANOTHER WORKBOOK

%3CLINGO-SUB%20id%3D%22lingo-sub-1938610%22%20slang%3D%22en-US%22%3EHELP%20ON%20REFERENCING%20ANOTHER%20WORKBOOK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1938610%22%20slang%3D%22en-US%22%3E%3CP%3EI%20wonder%20if%20anyone%20can%20help%20me.%26nbsp%3B%20I%20understand%20how%20to%20reference%20another%20workbook.%26nbsp%3B%20However%20I%20am%20wanting%20to%20link%20another%20wookbook%20where%20the%20workbook%20name%20is%20quoted%20in%20the%20current%20sheet.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EI.E%20%3D%5BWK01.xlsx%5DSheet1!%24A%241%3C%2FP%3E%3CP%3EHowever%20i%20am%20wanting%20to%20automatically%20get%20%5Bworkbook%20name%5D%20from%20column%20A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22410%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2290%22%3Eworkbook%3C%2FTD%3E%3CTD%20width%3D%22320%22%3EEARNINGS%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EMON%3C%2FTD%3E%3CTD%3ETUES%3C%2FTD%3E%3CTD%3EWED%3C%2FTD%3E%3CTD%3ETHURS%3C%2FTD%3E%3CTD%3EFRI%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EWK01%3C%2FTD%3E%3CTD%3E%C2%A3150.00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EWK02%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EWK3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EWK4%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EWK5%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EWK6%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EWK7%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EWK8%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EWK9%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EWK10%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1938610%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1938632%22%20slang%3D%22en-US%22%3ERe%3A%20HELP%20ON%20REFERENCING%20ANOTHER%20WORKBOOK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1938632%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F884740%22%20target%3D%22_blank%22%3E%40LEE_STEWART%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20might%20use%20the%20INDIRECT%20function%20for%20this%2C%20but%20that%20only%20works%20if%20the%20workbook%20referred%20to%20is%20open%20in%20Excel%2C%20so%20you'd%20have%20to%20keep%20WK01.xlsx%20to%20WK10.xlsx%20open.%3C%2FP%3E%0A%3CP%3ELet's%20say%20WK01%20is%20in%20cell%20A3.%3C%2FP%3E%0A%3CP%3EThe%20formula%20in%20B3%20could%20look%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDIRECT(%22'%5B%22%26amp%3BA3%26amp%3B%22.xlsx%5DSheet1'!A1%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20can%20be%20filled%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1938637%22%20slang%3D%22en-US%22%3ERe%3A%20HELP%20ON%20REFERENCING%20ANOTHER%20WORKBOOK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1938637%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F884740%22%20target%3D%22_blank%22%3E%40LEE_STEWART%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20agree%20with%20the%20advice%20given%20by%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20reference%20that%20may%20give%20further%20guidance%20on%20the%20use%20of%20INDIRECT%3A%20%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Findirect-named-range-different-sheet%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fformula%2Findirect-named-range-different-sheet%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1938640%22%20slang%3D%22en-US%22%3ERe%3A%20HELP%20ON%20REFERENCING%20ANOTHER%20WORKBOOK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1938640%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eok%20thankyou%2C%26nbsp%3B%20is%20there%20a%20way%20to%20do%20something%20similar%20where%20the%20wookbooks%20didn't%20have%20to%20be%20open.%26nbsp%3B%20As%20this%20would%20progress%20to%2052%20weeks%20over%20the%20course%20of%20the%20year%20and%20thats%20a%20lot%20off%20wookbooks%20to%20have%20open%20at%20any%20one%20time%20just%20to%20compare%20and%20add%20up%20over%20the%20course%20of%20the%20year%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1938645%22%20slang%3D%22en-US%22%3ERe%3A%20HELP%20ON%20REFERENCING%20ANOTHER%20WORKBOOK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1938645%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F884740%22%20target%3D%22_blank%22%3E%40LEE_STEWART%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20haven't%20said%20exactly%20how%20large%20those%20weekly%20workbooks%20are%2C%20how%20many%20distinct%20spreadsheets%20there%20are%20in%20each%20weekly%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20let%20me%20ask%20a%20question%20of%20two%3A%3C%2FP%3E%3COL%3E%3CLI%3Ewould%20it%20be%20possible%20to%20combine%20them%20as%20separate%20sheets%20all%20in%20the%20same%20workbook%3F%3C%2FLI%3E%3CLI%3Emight%20it%20even%20be%20possible%20to%20combine%20all%20of%20the%20data%20into%20a%20single%20data%20table%20(using%20column%20headings%20to%20differentiate%20days%20or%20weeks)%3F%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20been%20my%20observations%20that%20many%20people%20manually%20separate%20the%20raw%20data%20(the%20input%20end%20of%20things)%20into%20days%20or%20weeks%20for%20clarity%20at%20the%20front%20end.%20Excel%20is%20really%20good%20(it%20%22excels%22)%20in%20taking%20a%20single%20database%20(a.k.a.%20Table)%20and%20summarizing%20it%2C%20breaking%20it%20apart%20as%20needed%20for%20detail%20output%20reports%2C%20tabulating%20things.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20%3CSTRONG%3EPivot%20Table%3C%2FSTRONG%3E%20is%20but%20one%20example%20of%20that....your%20summary%20presentation%20could%20easily%20be%20produced%20if%20all%20that%20data%20were%20in%20a%20single%20table%20to%20begin%20with%2C%20letting%20Excel%20do%20the%20heavy%20lifting%20of%20producing%20the%20summary%20numbers%20by%20day%20and%20week.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1938651%22%20slang%3D%22en-US%22%3ERe%3A%20HELP%20ON%20REFERENCING%20ANOTHER%20WORKBOOK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1938651%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F884740%22%20target%3D%22_blank%22%3E%40LEE_STEWART%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20check%20this%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F28461672%2Freferencing-value-in-a-closed-excel-workbook-using-indirect%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EReferencing%20value%20in%20a%20closed%20Excel%20workbook%20using%20INDIRECT%3F%3C%2FA%3E%26nbsp%3Bif%20something%20will%20help%2C%20I%20didn't%20test%20what%20is%20suggested%20in%20it.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1938652%22%20slang%3D%22en-US%22%3ERe%3A%20HELP%20ON%20REFERENCING%20ANOTHER%20WORKBOOK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1938652%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eeach%20workbook%2Fweek%20is%2078kb%26nbsp%3B%20each%20day%20has%20its%20own%20sheet%20that%20calculates%20what%20I%20have%20earned%20depending%20on%20multiple%20factors%20with%20a%20summary%2Ftotal%20sheet%20at%20the%20end.%20There's%20then%20other%20sheets%20with%20references%20to%20pay%20rates%20drop%20down%20boxes%20and%20other%20sheets%20that%20determine%20variables%20on%20pay%20scales%20depending%20on%20certain%20circumstances.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20has%20all%20worked%20really%20well%20so%20far.%26nbsp%3B%20I%20am%20now%20wanting%20another%20workbook%20where%20I%20can%20compare%20analyse%20these%20weeks%20in%20one%20place.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1938653%22%20slang%3D%22en-US%22%3ERe%3A%20HELP%20ON%20REFERENCING%20ANOTHER%20WORKBOOK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1938653%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F884740%22%20target%3D%22_blank%22%3E%40LEE_STEWART%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20option%20could%20be%20Power%20Query%2C%20but%20that's%20rebuild%20the%20model%20from%20scratch.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1938655%22%20slang%3D%22en-US%22%3ERe%3A%20HELP%20ON%20REFERENCING%20ANOTHER%20WORKBOOK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1938655%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F884740%22%20target%3D%22_blank%22%3E%40LEE_STEWART%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20wrote%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CEM%3Eeach%20workbook%2Fweek%20is%2078kb%20each%20day%20has%20its%20own%20sheet%20that%20calculates%20what%20I%20have%20earned%20depending%20on%20multiple%20factors%20with%20a%20summary%2Ftotal%20sheet%20at%20the%20end.%20There's%20then%20other%20sheets%20with%20references%20to%20pay%20rates%20drop%20down%20boxes%20and%20other%20sheets%20that%20determine%20variables%20on%20pay%20scales%20depending%20on%20certain%20circumstances.%3C%2FEM%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CEM%3EThis%20has%20all%20worked%20really%20well%20so%20far.%20I%20am%20now%20wanting%20another%20workbook%20where%20I%20can%20compare%20analyse%20these%20weeks%20in%20one%20place.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhich%20basically%20supports%20my%20thesis%3A%20%3CU%3Eyou're%20doing%20the%20heavy%20lifting%3C%2FU%3E%20by%20separating%20everything%20as%20you've%20done%20it%2C%20making%20it%20harder%20for%20Excel%20now%20to%20do%20this%20big%20picture%20analyses.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%2C%20as%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%20correctly%20points%20out%2C%20require%20a%20total%20redesign%20to%20make%20it%20into%20a%20single%20database--but%20doing%20that%20would%20provide%20maximum%20flexibility%3A%20you'd%20then%20be%20able%20to%20continue%20doing%20your%20daily%20and%20weekly%20summaries%20as%20well%20as%20the%20big%20picture%20stuff.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I wonder if anyone can help me.  I understand how to reference another workbook.  However I am wanting to link another wookbook where the workbook name is quoted in the current sheet.  

I.E =[WK01.xlsx]Sheet1!$A$1

However i am wanting to automatically get [workbook name] from column A

 

workbookEARNINGS
 MONTUESWEDTHURSFRI
WK01£150.00    
WK02     
WK3     
WK4     
WK5      
WK6     
WK7     
WK8     
WK9     
WK10     

 

Thanks in advance

11 Replies

@LEE_STEWART 

You might use the INDIRECT function for this, but that only works if the workbook referred to is open in Excel, so you'd have to keep WK01.xlsx to WK10.xlsx open.

Let's say WK01 is in cell A3.

The formula in B3 could look like this:

 

=INDIRECT("'["&A3&".xlsx]Sheet1'!A1")

 

This can be filled down.

@LEE_STEWART 

 

I agree with the advice given by @Hans Vogelaar 

 

Here's a reference that may give further guidance on the use of INDIRECT: https://exceljet.net/formula/indirect-named-range-different-sheet

 

@Hans Vogelaar 

 

ok thankyou,  is there a way to do something similar where the wookbooks didn't have to be open.  As this would progress to 52 weeks over the course of the year and thats a lot off wookbooks to have open at any one time just to compare and add up over the course of the year?

@LEE_STEWART 

 

You haven't said exactly how large those weekly workbooks are, how many distinct spreadsheets there are in each weekly workbook.

 

So let me ask a question of two:

  1. would it be possible to combine them as separate sheets all in the same workbook?
  2. might it even be possible to combine all of the data into a single data table (using column headings to differentiate days or weeks)?

 

It's been my observations that many people manually separate the raw data (the input end of things) into days or weeks for clarity at the front end. Excel is really good (it "excels") in taking a single database (a.k.a. Table) and summarizing it, breaking it apart as needed for detail output reports, tabulating things.

 

The Pivot Table is but one example of that....your summary presentation could easily be produced if all that data were in a single table to begin with, letting Excel do the heavy lifting of producing the summary numbers by day and week.

@LEE_STEWART 

You may check this Referencing value in a closed Excel workbook using INDIRECT? if something will help, I didn't test what is suggested in it. 

@mathetes 

each workbook/week is 78kb  each day has its own sheet that calculates what I have earned depending on multiple factors with a summary/total sheet at the end. There's then other sheets with references to pay rates drop down boxes and other sheets that determine variables on pay scales depending on certain circumstances.

 

This has all worked really well so far.  I am now wanting another workbook where I can compare analyse these weeks in one place. 

@LEE_STEWART 

Another option could be Power Query, but that's rebuild the model from scratch.

@LEE_STEWART 

 

You wrote:

 

each workbook/week is 78kb each day has its own sheet that calculates what I have earned depending on multiple factors with a summary/total sheet at the end. There's then other sheets with references to pay rates drop down boxes and other sheets that determine variables on pay scales depending on certain circumstances.

 

This has all worked really well so far. I am now wanting another workbook where I can compare analyse these weeks in one place.

 

Which basically supports my thesis: you're doing the heavy lifting by separating everything as you've done it, making it harder for Excel now to do this big picture analyses.

 

It would, as @Sergei Baklan correctly points out, require a total redesign to make it into a single database--but doing that would provide maximum flexibility: you'd then be able to continue doing your daily and weekly summaries as well as the big picture stuff.

@mathetes  ok thankyou.  Not the end to this story I was expecting but take your advise on board.  Looks like I have a lot off work to re-do this.  The reason I guess I have ended up in this position is it has evolved over time.  To me wanting a quicker way to calculate what I earned in a day which is complicated in itself.  To then keeping a weekly copy of it too check my payslip when I get it a fortnight later to now thinking it'll be good to see this information for the year in one place.  

 

If I understand you correctly.  I need to move to one sheet = 1 week.  Therefore 52 sheets for the year with the summary on the 53rd sheet. which will require the wages calculation to appear 5 times on one sheet.

20201129_135122.jpg

 

@LEE_STEWART 

If I understand you correctly. I need to move to one sheet = 1 week. Therefore 52 sheets for the year with the summary on the 53rd sheet. which will require the wages calculation to appear 5 times on one sheet.

 

No. Simpler than that. Move to one sheet. Full stop.

 

One sheet that contains the raw data. Then learn how to use Excel's many features--I've mentioned the Pivot Table, @Sergei Baklan has referred to Power Query--but learn to use Excel's many features that can take extensive raw data and produce detailed summaries at whatever level of specificity you want.

  • Daily reports for specified days.
  • Weekly reports
  • Annual
  • Comparisons between days, weeks, years
  • Do we need to mention months?

 

By storing your raw data in daily sheets, you're producing one level of that but making all the others harder to do, since you then need to make the kind of cross workbook links you opened this thread talking about.

 

Your screen image shows part of the problem, in that you've made it, to a certain extent, a work of art, using colors and nice headings, etc., etc. That happens often, and it often happens as you say, it grows up over time.

 

Let the raw data be just that, raw data. Let your output reports be where you pretty things up.

 

There are YouTube videos and other resources here on Microsoft's website that can all help....look for help constructing what are called "Dashboards"--they generally are ways of producing useful summaries, working from collections of raw data.

 

And you can always get help here. You could begin by posting not just an image but some representative samples of your daily/weekly sheets. We could illustrate how a single data table could be constructed using that data. Maybe you should aim at implementing the new approach in 2021.

 

 

 

 

@LEE_STEWART 

 

Let me offer a bit more of an explanation to my last post. I know it sounds like, potentially, a lot of work to now consolidate weeks or days worth of separate spreadsheets into a single database. So let me offer a bit of my own reasoning. I have come to this from a career (retired now) that included not only a lot of spreadsheets but also a role as director of a major corporation's HR and Payroll database. I have quite a bit of experience taking separate data tables and combining them to produce useful reports. I also have experience taking single tables in Excel and extracting data to produce useful summary reports.

 

Excel is a very powerful tool for analyzing data. Unfortunately, it is quite common for people to do what you've done here, which is to start at the lower level with daily or weekly sheets that summarize sales/orders/purchases/production at that daily or weekly level. Often these sheets are essentially little more than automated copies of the kinds of records that previously were kept in paper ledger sheets. It is true that Excel can work to pull together multiple sheets into a single report. You were starting with one method of doing that. Reference has also been made to Power Query, which is, as the label implies, a powerful way to query multiple tables, joining them so as to produce useful reports. It's quite comparable to what I used to do with the HR/Payroll database at my company, where SQL (Structured Query Language) enabled us to produce reports that joined tens of separate data tables into a single "virtual table" for a variety of purposes.

 

Excel can do that.

 

Excel also has a number of tools that can take a single data table, a table that consolidates years worth of daily data, say, and extract and summarize very focused subsets of the whole. I've already mentioned the Pivot Table, which many find to be adequate for most such analyses. There are also both menu items and, recently a function for FILTERing or SORTing selected data. Coupled with other functions such as MIN and MAX, AVERAGE, etc., etc., there truly is a wide array of summary reports that are readily produced from a single database that contains all the micro-level ("highly granular" is the way a lot of data geeks refer to it) details.

 

And the reality is that it's easier and more flexible to use Excel's abilities to focus and extract, to filter, to cross-tabulate, from a single database, than it is to take a host of separate spreadsheets and combine them (via Power Query) so as to do that same cross-tabulation. It's one thing if all you're combining are, say, three or four separate workbooks, but when your source data for a year has been captured in 52 separate workbooks, then it becomes quite unwieldly. Possible, but unwieldly.