spacecaptainsuperguy
Board Regular
- Joined
- Dec 30, 2004
- Messages
- 202
- Office Version
- 365
- Platform
- Windows
I'm hoping someone can put me on the path to make life a bit easier. I've got a hand full of spreadsheet that live in various folders on our network. The details may not be all that important but I'll include them in case there are any specific recommendations, but the short version is I'm having trouble with these files updating their formulas when I open them, even when Excel asks if I want to update them upon opening and that is ultimately the problem I'm looking to solve as my co-workers are not that great with Excel and expect things to just work, especially when you click to update the formulas.
The majority of these spreadsheet let's call "cash sheets" and function more or less like a check register for various loans. They all sit in a folder on the network (S drive). There is another spreadsheet let's call the "loan summary" that houses all the details of various loans (company, bank name, interest rates, note balance, etc) and this one resides in a different folder on the S drive.
The "loan summary" spreadsheet is set up as a table and gets the note balances for all the loans from the various "cash sheets" and the cash sheets get loan specific information (interest rates, bank name, etc) from the "loan summary" spreadsheet.
The formula for the note balance in the "loan summary" spreadsheet is a simple, equal whatever is in the note balance cell on the "cash sheet". For example: ='S:\CASH SHEETS\[CYPRESS INVESTMENTS.xlsx]4018 THCB'!$F$6 is one of them. Cell F6 has the loan balance for this note and that info needs to be fed into the "loan summary" spreadsheet.
The formulas in the "cash sheet" pull information about the loan into those spreadsheet via an XLOOKUP formula. I'm aware that XLOOKUP does not really like to play as nice as others when linked to other spreadsheets when it comes to updating formulas, unless that spreadsheet is also open. An example of one of those formulas is: =XLOOKUP(CONCAT($F$1,$F$2),'S:\NOTE PAYABLE SCHEDULES\ALL MONTHLY NOTE PAYMENTS.xlsx'!Note_Database[UNIQUE LOAN ID],'S:\NOTE PAYABLE SCHEDULES\ALL MONTHLY NOTE PAYMENTS.xlsx'!Note_Database[CURRENT INTEREST RATE],"n/a",0). This formula looks up the loans unique ID# and returns that loans current interest rate.
One thing I've considered is changing all of these spreadsheet to a .xlsm file extension to add a macro that causes them to open any files they are linked to and then close them so that the formulas can update and my co-workers can rest assured that the information presented is the latest information and hopefully avoid all the #REF errors that we routinely get when the XLOOKUP formulas fail to update.
I'm sure that has to be a better tactic out there, but I can't think of what it might be and apparently cannot think of the right terms to have google or this forum provide me with any answers specific to my needs.
If I can provide any other details, please let me know. Any advice is much appreciated.
The majority of these spreadsheet let's call "cash sheets" and function more or less like a check register for various loans. They all sit in a folder on the network (S drive). There is another spreadsheet let's call the "loan summary" that houses all the details of various loans (company, bank name, interest rates, note balance, etc) and this one resides in a different folder on the S drive.
The "loan summary" spreadsheet is set up as a table and gets the note balances for all the loans from the various "cash sheets" and the cash sheets get loan specific information (interest rates, bank name, etc) from the "loan summary" spreadsheet.
The formula for the note balance in the "loan summary" spreadsheet is a simple, equal whatever is in the note balance cell on the "cash sheet". For example: ='S:\CASH SHEETS\[CYPRESS INVESTMENTS.xlsx]4018 THCB'!$F$6 is one of them. Cell F6 has the loan balance for this note and that info needs to be fed into the "loan summary" spreadsheet.
The formulas in the "cash sheet" pull information about the loan into those spreadsheet via an XLOOKUP formula. I'm aware that XLOOKUP does not really like to play as nice as others when linked to other spreadsheets when it comes to updating formulas, unless that spreadsheet is also open. An example of one of those formulas is: =XLOOKUP(CONCAT($F$1,$F$2),'S:\NOTE PAYABLE SCHEDULES\ALL MONTHLY NOTE PAYMENTS.xlsx'!Note_Database[UNIQUE LOAN ID],'S:\NOTE PAYABLE SCHEDULES\ALL MONTHLY NOTE PAYMENTS.xlsx'!Note_Database[CURRENT INTEREST RATE],"n/a",0). This formula looks up the loans unique ID# and returns that loans current interest rate.
One thing I've considered is changing all of these spreadsheet to a .xlsm file extension to add a macro that causes them to open any files they are linked to and then close them so that the formulas can update and my co-workers can rest assured that the information presented is the latest information and hopefully avoid all the #REF errors that we routinely get when the XLOOKUP formulas fail to update.
I'm sure that has to be a better tactic out there, but I can't think of what it might be and apparently cannot think of the right terms to have google or this forum provide me with any answers specific to my needs.
If I can provide any other details, please let me know. Any advice is much appreciated.
