I am creating a spreadsheet to track monthly rentals. Every month I create a “Rent Roll” file that contains the data for all of the tenants for that month. I am using external data to create a ledger for each tenant in separate files from the data in the Rent Roll files… I have dragged the formula down in the legers to create an entry in the ledger every month for the next 10 years. Everything works fine, but for the months that I have not yet created a rent roll file, I get the “The following external file could not be loaded. Data linked from this file did not get updated” Error. Is there a way to have calc not display this error? I know it’s because the Rent Roll files have not been created for future months, I’m just looking to have the errors to not be displayed.
No.
Hi, Could you add a status flag in the summary file that only gets set to 1 on a particular day/date in the new month, and you only look for the data in the external file when the status is set to 1. Are your “Rent roll” files always created on the same day every month, if so then you could have the status automatically set.
As an example in the purchase summary file I want to show the total purchases for each month, so using functions INDIRECT and CONCATENATE to build the following SUM,
=IF(C53=1,SUM(INDIRECT(CONCATENATE($E$1,B53,$G$1))),0)
C53 is the status 0 or 1
E1 is the location and title of the file eg, ‘file:///D:/AAAA/XYZ/Purchases_
B53 is the month I wanted eg, 202201
G1 is the range to sum eg .xlsx’#$Sheet1.$B$4:$B$1000
So on the 1st working day of each month, the monthly purchase file is created, and the status flag in the summary file is manually set to 1, so that until the status flag for the month is set to 1, no attempt is made to reference the external file.
Let us know if it helps.
Used in Windows 10 Home, LO 7.2.5.2
Yes, that was very helpful. I used an existing column that contains the date in text format as the flag, so my formula looks like this:
=IF(TODAY()>=DATEVALUE(A24),INDIRECT("‘file:///home/matt/Documents/Clinton Estates/Rent Roll/"&I24&"/Clinton Rent “&J24&”.ods’#$rent.G2"),"")
Obviously I get a #REF error if I don’t create the file before that date. I know that I could use IFERROR to suppress that error, but I don’t mind dismissing one error, and it will be a reminder that I need to get my rent roll completed for that month for the data in the ledgers to be correct.
Thank you very much for your solution!
Matt