I have two linked Excel workbooks stored in separate SharePoint document libraries. The source workbook is created daily by a third-party application (the original file is overwritten by one with the same filename) and is in the .xls format. The destination workbook links to the raw data in the source workbook and turns it into a report - this is saved in the .xlsx format.

My goal is to have the destination workbook update automatically based on changes to the source workbook - without having to open to the source. Right now I am close, but I'm running into an issue that I can't figure out myself. When the source workbook is overwritten with new data, I open the destination workbook and find that all of the numbers in my report have updated properly, but any cells that should contain text now display "#N/A" (Value Not Available Error). If I then open the source workbook, all of my text updates and displays correctly.

Does anyone have any insight as to why my numbers are updating correctly but text is not (all of the data is coming from the same source workbook)? Ideally, I'm looking for a solution that does not involve adding a macro or code to the destination workbook, but I'll listen to any suggestions.

Please let me know if there are additional details I should provide.

Thank you for taking interest in my problem.