It took a couple of exports and Excel configurations to duplicate your results, Hirono but I was able to do so.
I think that this problem relates more to Excel 2003 (haven't tried it with 2007) and how it recalculates workbooks than it is a problem with how Monarch handles or creates the Excel export when appending data to an existing file and overwriting worksheets.
You're correct in thinking that your solution lies in creating a macro, but you won't have to run it manually yourself every time that you open the file. So it should be an easy fix that will not become more work later.
What you want to do is create a short macro that runs automatically every time /Bthat the file is opened. To do so, go to the Visual Basic Editor. Now find the item that is named ThisWorkbook in the project window. This is probably on the left side of the editor window. Double click the ThisWorkbook item.
Copy the following program code and paste it into the editor in the main document window:
Private Sub Workbook_Open()
Literally, this means that every time the the workbook is opened, Excel will perform a full recalculation of every formula in the workbook. This is a more extensive recalculation than normal, or if you just hit the F9 key to force Excel to recalculate.
Now save and close the file. When you open it, Excel may prompt you to Enable or Disable macros. Choose Enable and you should find that the value in Sheet1 cell A1 is now always reflecting the correct value in Sheet2 cell A1.
My testing shows that the macro code that you have added should safely stay in the file when you replace the Sheet2 data with future Monarch project exports.
Your use of a formula that automatically links to freshly exported Monarch data is a great use of the combination of Excel and Monarch together, and is something that I've been using in my work for a long time. Further, when you combine the automatic refresh for a pivot table with updated Monarch data (even though you can now export pivot tables directly with Monarch v10), you have the makings for a solid foundation for great dashboard style Excel reports.
Please advise if this resolves your problem.
As a matter of fact exist some tools for resolving troubles with excel documents and one of them consist of only a tool. It was my theory and not more. But some times this application saved my time. It would be helpful solution for solving varied kinds of excel problems - url=http://www.recoverytoolbox.com/recover_excel_files.htmlrepair recovered Excel file[/url].
I also have this problem. I have one machine with Monarch 10.5 and Excel 2010. When I export, the summary updates properly in 2010. If I open the same spreadsheet in 2003, the summary links do not update. I then exported my info using Monarch 9.0 and the 2003 spreadsheet updated properly. This leaves me to believe there is something different in 10.5 with this kind of export. This was the first time I used Monarch 10.5.