Home

OLD Dog looking for a Better Way to Link Worksheets & Workbooks and Create Reports than AutoSum

%3CLINGO-SUB%20id%3D%22lingo-sub-758001%22%20slang%3D%22en-US%22%3EOLD%20Dog%20looking%20for%20a%20Better%20Way%20to%20Link%20Worksheets%20%26amp%3B%20Workbooks%20and%20Create%20Reports%20than%20AutoSum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-758001%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20for%20a%20long%20time%20I%20have%20just%20used%20AutoSum%20to%20link%20up%20a%20field%20from%20another%20worksheet%20or%20workbook%20and%20it's%20pretty%20tedius.%26nbsp%3B%20So%20this%20old%20dog%20is%20trying%20to%20find%20a%20better%20way.%26nbsp%3B%20%26nbsp%3BThe%20project%20is%20a%20Reporting%20methodology%20for%20Privately%20held%20Loans.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20%3CSTRONG%3EBorrower%20WorkBook%3C%2FSTRONG%3Es%20are%20for%20each%20of%208%20Borrowers%20which%26nbsp%3B%3CSPAN%3Ehave%20around%205%20to%2010%20%3CSTRONG%3ELoan%20Worksheets%3C%2FSTRONG%3Efor%20Each%20loan%20that%20are%20calculating%20interest%20accruals%20and%2For%20any%20interest%20or%20principal%20payments%20made%20and%20one%20%3CSTRONG%3EConsolidated%20Worksheet%3C%2FSTRONG%3Ethat%20serves%20as%20a%20general%20overview%20of%20all%20of%20the%20different%20loans%20within%20that%20Borrower%20WorkBook.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThere%20are%203%20different%20Entities%20doing%20the%20Lending%20with%20one%20%3CSTRONG%3EOverview%20WorkBook%3C%2FSTRONG%3Ethat%20provides%20an%20overview%20of%20all%20the%20loans%20outstanding%20and%20the%20important%20totals%20in%20two%20%3CSTRONG%3EOverview%20WorkSheet%3C%2FSTRONG%3Es%20(2%20Entities%20on%20one%20and%201%20on%20the%20other).%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EMonthly%20-%20I%20pull%20up%20the%20various%20spreadsheets%20and%20do%20calculations%20or%20entries%20in%20the%20individual%20Borrower%20Worksheets%2C%20but%20having%20those%20changes%20update%20in%20the%20Overview%20WorkBook%20is%20proving%20challenging.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EOkay%20so%20don't%20judge%2C%20but%20I%20created%20those%20relationships%20between%20the%20WorkSheets%20or%20WorkBooks%20using%20the%20AutoSum%20button%20on%20the%20main%20Home%20Bar%20-%20once%20for%20each%20field%20that%20links%20with%20the%20appropriate%20field%20on%20the%20corresponding%20workbook.%20A%20lotta%20fields...%20Updates%20when%20opening%20the%20Overview%20WorkBook%20is%20not%20working%20properly.%26nbsp%3B%20Every%20year%20too%2C%20I%20have%20to%20redo%20all%20those%20links%20-%26nbsp%3B%3C%2FSPAN%3ECouple%20hundred%20of%20them.%20.%20If%20I%20have%20to%20add%20another%20borrower%20or%20Loan%20-%20More%20tracking%20down%20and%20updating%20links...%20Ruff!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20briefly%20popped%20onto%20the%20site%20and%20watched%20a%20couple%20of%20tutorials%20on%20Get%20%26amp%3B%20Transform%20and%20Power%20Pivot%20but%20I'm%20not%20really%20certain%20how%20that%20works%20exactly%3B%20if%20it's%20the%20right%20method%20or%20how%20to%20apply%20it%20in%20this%20situation.%26nbsp%3B%20%26nbsp%3BSO%20my%20specific%20request%20is%20to%20ask%20you%20all%20awesome%20excel%20spreadsheet%20Gods%20and%20Goddesses%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3E%3CFONT%20face%3D%22inherit%22%3EWhat%20should%20I%20be%20using%20to%20link%20these%20Spreadsheets%20to%20provide%20the%20most%20efficient%20and%20%3C%2FFONT%3Eupdate-able%3CFONT%20face%3D%22inherit%22%3E%26nbsp%3Bformats%3F%3C%2FFONT%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CFONT%20face%3D%22inherit%22%3EI've%20attached%20a%20couple%20of%20PDF%20examples%20of%20the%20spreadsheets%20that%20I've%20got%20so%20far.%26nbsp%3B%20%3C%2FFONT%3E%3C%2FP%3E%3CUL%3E%3CLI%3E%3CFONT%20face%3D%22inherit%22%3EWhat's%20the%20best%20way%20to%20move%20forward%3F%26nbsp%3B%3C%2FFONT%3E%3C%2FLI%3E%3CLI%3E%3CFONT%20face%3D%22inherit%22%3EWhat's%20the%20best%20function(s)%20within%20Excel%20to%20make%20this%20work%3F%20%3C%2FFONT%3E%3C%2FLI%3E%3CLI%3E%3CFONT%20face%3D%22inherit%22%3EDo%20I%20start%20from%20total%20Scratch%20or%20a%20combination%20of%20Salvage%20This%20and%20Start%20That%20over%20again%3F%3C%2FFONT%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EWhat%20are%20the%20best%20resources%2C%20tutorials%20or%20Videos%20to%20help%20me%20get%20a%20better%20grasp%20for%20learning%20those%20functions%20or%20methods%20that%20you%20recommend%3F%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20ever%20so%20much%20for%20your%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERenee%20-%20Barking%20on%20the%20Porch!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-758001%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-758696%22%20slang%3D%22en-US%22%3ERe%3A%20OLD%20Dog%20looking%20for%20a%20Better%20Way%20to%20Link%20Worksheets%20%26amp%3B%20Workbooks%20and%20Create%20Reports%20than%20Auto%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-758696%22%20slang%3D%22en-US%22%3E%3CP%3EHard%20to%20tell.%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20sounds%20like%20you%20might%20want%20VBA%20to%20help%20import%20the%20data%2C%20create%20a%20backend%20table%20of%20data%2C%20then%20your%20reports%20can%20run%20from%20that.%20My%20bread%20and%20butter%20but%20is%20complicated%20depending%20on%20structure%20of%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20a%20way%20more%20up%20your%20street%20if%20you%20understand%20links%2C%20and%20willing%20to%20learn%20power%20query.%3C%2FP%3E%3CP%3E1.%20Setup%20a%20%22backend%22%20table%20to%20get%20your%20data%20into%201%20row%20on%20the%20individual%20workbook.%3C%2FP%3E%3CP%3E2.%20On%20your%20%22Main%22%20workbook%2C%20make%20a%20folder%20query%20to%20pick%20up%20.xlsms%20with%20those%20tables.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20some%20examples%3C%2FP%3E%3CP%3EYou%20will%20need%20to%20amend%20one%20of%20the%20folder%20path%20to%20your%20folder.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20986px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F123532iD37C2A116874282F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.PNG%22%20title%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHopefully%20this%20helps%3F%3CBR%20%2F%3EAlistair%20Blades%20-%20Bespoke%20Excel%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F377307%22%20target%3D%22_blank%22%3E%40RMP_AZ%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
RMP_AZ
Occasional Visitor

So for a long time I have just used AutoSum to link up a field from another worksheet or workbook and it's pretty tedius.  So this old dog is trying to find a better way.   The project is a Reporting methodology for Privately held Loans. 

 

The Borrower WorkBooks are for each of 8 Borrowers which have around 5 to 10 Loan Worksheets for Each loan that are calculating interest accruals and/or any interest or principal payments made and one Consolidated Worksheet that serves as a general overview of all of the different loans within that Borrower WorkBook.

 

There are 3 different Entities doing the Lending with one Overview WorkBook that provides an overview of all the loans outstanding and the important totals in two Overview WorkSheets (2 Entities on one and 1 on the other). 

 

Monthly - I pull up the various spreadsheets and do calculations or entries in the individual Borrower Worksheets, but having those changes update in the Overview WorkBook is proving challenging.

 

Okay so don't judge, but I created those relationships between the WorkSheets or WorkBooks using the AutoSum button on the main Home Bar - once for each field that links with the appropriate field on the corresponding workbook. A lotta fields... Updates when opening the Overview WorkBook is not working properly.  Every year too, I have to redo all those links - Couple hundred of them. . If I have to add another borrower or Loan - More tracking down and updating links... Ruff!

 

I briefly popped onto the site and watched a couple of tutorials on Get & Transform and Power Pivot but I'm not really certain how that works exactly; if it's the right method or how to apply it in this situation.   SO my specific request is to ask you all awesome excel spreadsheet Gods and Goddesses:

 

  • What should I be using to link these Spreadsheets to provide the most efficient and update-able formats?

I've attached a couple of PDF examples of the spreadsheets that I've got so far. 

  • What's the best way to move forward? 
  • What's the best function(s) within Excel to make this work?
  • Do I start from total Scratch or a combination of Salvage This and Start That over again?

 

  • What are the best resources, tutorials or Videos to help me get a better grasp for learning those functions or methods that you recommend?

 

Thank you ever so much for your help!

 

Renee - Barking on the Porch!

1 Reply

Hard to tell.

It sounds like you might want VBA to help import the data, create a backend table of data, then your reports can run from that. My bread and butter but is complicated depending on structure of workbook.

 

Maybe a way more up your street if you understand links, and willing to learn power query.

1. Setup a "backend" table to get your data into 1 row on the individual workbook.

2. On your "Main" workbook, make a folder query to pick up .xlsms with those tables.

 

I've attached some examples

You will need to amend one of the folder path to your folder.

Capture.PNG

 

Hopefully this helps?
Alistair Blades - Bespoke Excel

@RMP_AZ 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies