locked
Excel 2010 linked cells not updating RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have what is a very simple workbook with a number of different sheets in it. Data in some of the sheets is updated using Access queries (let's call these Sheets A and B). Then there are several other sheets (Sheets C and D). Cells on Sheets C and D are linked to the cells on Sheets A and B so that the data is updated. Each cell is linked separately to a cell or range of cells on Sheets A and B, no fancy formulas or named ranges. This has been working properly through several updates of Access from 2000 to 2003. I've now updated to Office 2010.  None of the links or formulas have been changed, but I did create a new workbook in Excel 2010 and then copy all of the formulas and worksheets from the old Office 2003 workbook.

    What's happened is that none of the linked cells on Sheets C and D are updating properly. The Access queries run and the data on Sheets A and B are updated properly. However, when I go to Sheets C and D, it shows the data from the BEFORE Sheets A and B were updated. I have to enter each cell and press F2 to get the data to update.  This is ridiculous and I can't imagine why all of a sudden this is not working.  I did notice that for some reason all of the cells are Locked, but unlocking them doesn't change anything.

    Deb
    Friday, March 18, 2011 4:06 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi

    This might be down to the copying you did. When you copy a formula to another workbook Excel will try to maintain the original source of the formula.  If this is the case the formulas on C and D are linked back to you original 2003 workbook.

    Look at the formulas and check there isn't a reference to the original file (in []) added to your formulas.

    If you have links you should be able to remove them via the Edit Links button on the Data tab.

    Hope this helps

    G North MMI
    • Marked as answer by Sally Tang Monday, March 21, 2011 5:08 AM
    Friday, March 18, 2011 5:20 PM

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi

    This might be down to the copying you did. When you copy a formula to another workbook Excel will try to maintain the original source of the formula.  If this is the case the formulas on C and D are linked back to you original 2003 workbook.

    Look at the formulas and check there isn't a reference to the original file (in []) added to your formulas.

    If you have links you should be able to remove them via the Edit Links button on the Data tab.

    Hope this helps

    G North MMI
    • Marked as answer by Sally Tang Monday, March 21, 2011 5:08 AM
    Friday, March 18, 2011 5:20 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    This is not a problem of links being directed to the wrong location.  The formulas look perfectly fine and are linked to the correct cells in the same workbook.

    Deb
    Wednesday, March 23, 2011 3:56 PM
  • Question
    Sign in to vote
    3
    Sign in to vote

    What happens if you press F9 (anywhere on the sheet in question)?

    If the values recalculate then the workbook appears to have switched to manual recalculation.  Look in File->Options->Formulas for settings.

     

    G North MMI
    Wednesday, March 23, 2011 4:54 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Nothing happens when I press F9. I made sure that recalculation was set to "automatic." I have to actually enter the cell, click in the Formula bar (not just in the cell) and then press Enter to update the data. It's as if Excel doesn't even recognize that the cell contains a formula. It acts as if the value in the cell is a static number until I go to the Formula bar and refresh it.
    Deb
    Thursday, March 24, 2011 3:46 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I get the same problem - it appears as if the cell is converting the formula to text in the cell.

     

    For instance, I have =refFundCostCenters!B9 in a cell and it is calculating a value.  When I try to change it to a !B10, the whole thing converts to text in the cell rather than a formula.


    I messed around with CTRL+~ to show hide formulae and that seems to have cleared up the issue.  Not sure it is related, but worth a try.
    Monday, September 12, 2011 8:21 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I fixed this issue by doing file save as and saving as an excel 2010 workbook type. After i did that I got a bunch of errors from the file but then I was forced to close and reopen and the links were updating. I think the issue was that the sheet was in some kind of compatibility mode that disabled some of the features.

    Hope this helps you too!

    Tuesday, January 17, 2012 7:38 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    We had been using 2003 and updated to 2010.. the links were not updating

    We opened the files and saved them as 2010 workbooks, this fixed the problem with the links

    Tuesday, September 18, 2012 1:50 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I'm encountering the exact same problem with Excel 2010: Linked values within a spreadsheet are not updating.  The formula is =Summary!B23 but when I change the value of cell Summary!B23 the value of the linked cell on the other worksheet does not change.  If I click on the linked cell and then click on the formula, it will update.  I do have recalulate set to Automatic (Options > Formulas > Calculation Options > Automatic)   Clicking on F9 doesn't help either.  I even turned off Automatic calculation and then turned it back on but that didn't help either.

    The source cell is also a link to another cell on the same worksheet (formula =B15).  I've been using copies of this spreadshet for months without encountering this error.  If you like, I can email the spreadsheet to  you.

    Version of Excel is 14.0.6.6129.5000 (32 bit). 

    Wednesday, March 13, 2013 4:11 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    The final solution I found to the problem reported in my OP, IIRC, was to save the workbook as a macro-enabled workbook (file type .xlsm). I don't know if this would resolve your issue, but you could give it a try.

    Deb

    • Proposed as answer by JadeMum Sunday, May 26, 2013 9:53 PM
    • Unproposed as answer by JadeMum Sunday, May 26, 2013 9:53 PM
    Wednesday, March 13, 2013 5:07 PM
  • Question
    Sign in to vote
    1
    Sign in to vote
    The easiest solution was to change the cell the from Text to General and then make sure Show Formulas was not highlighted on the Formulas tab. Fixed it straight away.
    Sunday, May 26, 2013 9:55 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    After using a spreadsheet for years, I started to have this same problem.

            "For instance, I have =refFundCostCenters!B9 in a cell and it is calculating a value.  When I try to change it to a !B10, the whole thing converts to text in the cell rather than a formula."

    The fix posted by JadeMum on 5/26/13 worked for me as well.

            "The easiest solution was to change the cell the from Text to General and then make sure Show Formulas was not highlighted on the Formulas tab. Fixed it straight away."

    Thursday, April 17, 2014 4:44 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    I realize this thread is years old, but I was having exactly the same problem with exporting data from Access into Excel and trying to update values on one worksheet based on the information imported from Access (located on another worksheet in the same workbook). After much research and some experimenting I found something that works for me.

    I tried the macro enabled approach, but I could not figure out how to set Access to export to a macro-enabled workbook, it seems to only export to .xlsx files, not .xslm. So i found some advice to try the  pressing F9 to re-calculate approach, which by itself did not work. However, when I pressed Ctrl+Alt+F9, it recalculates the whole sheet. No macros necessary and no tedious manual approach either.

    Also while playing with it, it seems that if I have the Excel workbook open while doing the export, then it re-calculates without using the above approach.

    Hopefully this helps. And hopefully it keeps working for me. Seems an obvious weakness in Excel, but for the time being anyway, I found this a working option.

    Wednesday, July 2, 2014 4:16 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you...that was my problem. Your solution worked for me.
    Wednesday, August 6, 2014 3:19 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    In terms of a solution to this, I also found that "Enabling Multi-threaded calculation" and using "at least 4 threads" also fixes this issue.
    Friday, July 21, 2017 8:46 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Though I clearly know of the outdatedness of this topic. 

    But the same happened to me and I solved the same by saving the workbook as a macro-free workbook (earlier it was Macro Enabled WB).

    Just wanted to contribute mine. :)

    Saturday, July 22, 2017 11:51 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks, this worked great for me
    Saturday, October 14, 2017 2:23 PM