Excel 2008 : Cells with External References NOT Updating

**tmcdaniel** · 02-29-2012, 12:44 PM

I am using Excel 2010 and have a workbook with two sheets. Sheet #1 is formatted in a printable form layout that is used by a department in our company and each cell on sheet #1 has a link to a cell in sheet #2. Sheet #2 is a simple table format (rows and columns). Sheet #2 is updated using a SSIS package. The whole process worked fine with Excel 2003 but now with Excel 2010 the Sheet #1 cells are not updating with the new values contained in sheet #2. I have to manually force the vallues to show up by clicking into each cell on sheet #1, pressing the F2 key then hitting the Enter key.

How can I get the referenced cell values to update automatically??

**JieJenn** · 02-29-2012, 12:47 PM

On your formulas ribbon, all the way to the right there's a calculation group. Click on Calculation Options make sure your calculation options is set to automatic. If that doesn't work would you mind attach a sample file.

**Richard Buttrey** · 02-29-2012, 12:49 PM

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

**tmcdaniel** · 02-29-2012, 01:35 PM

The Calculations is set to Automatic.

I am attaching two spreadsheet files: one is used as a "template" and contains only the labels and formulas; the second is a copy of the "template" and is actually populated with data. File #2 is the end result of the SSIS package process and contains actual data, although I did change some name and price information to protect the innocent and when I made these manual changes to the 2nd sheet (IncomingData) the changes were reflected in the 1st sheet (Blank Form) which as the references to the 2nd sheet.

So if I manually click on a cell on sheet #1 (Blank Form) then press F2 and Enter the referenced values are displayed OR if I go to sheet #2 (IncomingData) and manually change data in cells those referenced values then show up on sheet #1.

**tmcdaniel** · 02-29-2012, 01:40 PM

So it seems the process of updating sheet #2 with data (from SSIS) is not being recognized by sheet #1 since it is occuring outside of Excel?? This worked flawlessly in Excel 2003. Why does F@$K!#G Microsoft continually change how things work?! Why can't they maintain backwards compatibility?!!!

**tmcdaniel** · 02-29-2012, 04:05 PM

Any ideas?

**JieJenn** · 02-29-2012, 04:17 PM

I guess it is a bug indeed.

**Richard Buttrey** · 02-29-2012, 04:36 PM

Hi,

I'm not clear what your problem is. Changes to Incoming data are reflected on the 'Blank Form' sheet. Please clarify.

**tmcdaniel** · 02-29-2012, 06:34 PM

Changes to 'IncomingData' sheet are NOT reflected on 'Blank Form' sheet when the changes are applied from outside Excel.

The 'IncomingData' sheet is populated by a SSIS package process and NONE of these changes are reflected on the 'Blank Form' sheet.

The data in the 'IncomingData' sheet is complete and you can see that most of this data is NOT reflected on the 'Blank Form' sheet, except for the Cost, Name and Email fields that I manually modified.

If the 'IncomingData' sheet data is modified within Excel the changes are reflected in the 'Blank Form' sheet.

Or if you go to each cell on the 'Blank Form' sheet and press F2 then press Enter the values from 'IncomingData' sheet will display on the 'Blank Form' sheet.

But both of these means are not acceptable as the process of creating the spreadsheet file is automated and users only look at the 'Blank Form' sheet where nothing displays after the automated process runs.