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!
