I am trying to automate a workbook to compile a weekly report. The workbook
is made up of a control sheet which the end user will use to run the various
macros, spreadsheets with reports (the reports are pulling from a websource)
then lookup sheets in which the formulas are references.

The issue I am having now is all of the report sheets have formula columns
for different reasons. Whenever I "refresh" the report from the outside
source, the formulas are being changed, even though the formula cell
references are absolute. This report cannot be automated with this happening
as the end user would have to fix all the formulas and I want to avoid this
as most end users who will run this report don't even know or understand
formulas.

When the reports refresh, the cells automatically resize to that of the
corresponding fields and I think might be what is interfering with the
formulas.

Can anyone help?

FYI The websource is Salesforce.com and I am using their Connect for Office.
As an alternative, I know I can build more worksheets into the workbook with
macros to copy the data to the sheets with the formulas, however, I wanted to
avoid that...