Publish and Sync Sharepoint Lists using Excel 2010

[Capt.Ragnar]

Alright gents:</SPAN>

I have searched and searched for a solution/work around for this issue, I’m hopeful someone smarter than I can produce an option that will satisfy the quandary described below…
</SPAN>
I have authored a spreadsheet that performs various analyitical tasks and compiles a summary report in table format that is then published to Sharepoint as a List. The problem occurs that at the point in time the sync occurs. All cell references to source data within the table are converted to values just before the sync and then remain as values afterwards.
</SPAN>
Subsequently, I’m left with a table that is values only in a report that must be updated monthly.</SPAN>

Is there a macro/setting/option that would allow the table to preserve the formulas and still sync values to SharePoint?

At current, I’m considering a simple copy and paste of an identical table each month over the table being sync’d to sharepoint to reflect the updates. </SPAN>

Any help is appreciated.

</SPAN>

 

[Capt.Ragnar]

bump...

 

[Weazel]

Well, I'm not so sure this is going to address your exact issue, but, I had a similar issue a while back and ended up installing the Excel 2007 Add-in: Synchronizing Tables with SharePoint Lists addin which is at http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=9345 and you can read about it here http://itblog.wolthaus.net/2012/03/synchronize-excel-2010-with-sharepoint-2010-list/
Hopefully this helps.

 

[Capt.Ragnar]

I'm using the 2007 add-in. Did you not experience the same issue with the tables converting to values only post sync?

 

[Weazel]

Yeah, unfortunately the issue is by design and I was never able to find a functional way of getting around it.
I've seen a few commercial applications that claim they work but I've never tried them since the corporation would probably not pay for it anyway.
In lieu of that, I've just modified the way I update data, i.e. some of the data I keep as a full spreadsheet which each user has to open locally or if its quasi dynamic data I just update it and sync it to the list or table.