Excel 2013/2016 not updating values while workbook is minimized












4















I have the following setup:




  1. 1 Workbook named Workbook1 with the value 123 in A1

  2. 1 Workbook named Workbook2 with value =[Workbook1.xlsx]Sheet1!$A$1 in A1


Now, when I go and change the value of Workbook1 to something else the value gets updated as expected in Workbook2.



But sadly, when I minimize Workbook2 and than change the value in Workbook1 and than restore Workbook2 to verify if the value has been updated it still shows the old value.



Notice:




  • after restoring the window even if I calculate (F9) or
    calculate the whole sheet (SHIFT + F9) the value doesn't update


  • when I add a new Worksheet to Workbook2 the value gets updated (was more a coincidence that I found that)



Is there a way to fix this?



Update:



As Máté Juhász already mentioned in the comments there are various ways to actually update the value.



I myself found that switching the View triggers the cell to update or simply chaning the Display ruler, Display gridlines, Display headers property they all triggered the cell to update its value.



I also had a look at:



MsgBox Application.Range("A1").Value
MsgBox Application.Range("A1").Text


Both, output the correct value while the old value is still visible.



So a thought came up that all those operations that trigger the cell have one thing in common the screen gets updated, so my current "solution" is the following:



Private Sub Workbook_WindowResize(ByVal Wn As Window)

Dim oldValue As Boolean
oldValue = Application.ScreenUpdating
Application.ScreenUpdating = Not oldValue
Application.ScreenUpdating = oldValue

End Sub


With the knowledge of the issue just being a not updating screen, I also tried to simply scroll my cell out of the visible range and scroll back - this also did update the cell's value.



Also, now it makes sense why F9 and SHIFT + F9 didn't trigger the cell to update, since the value is already correct, there just simply isn't a thing for the calculation to update - since it's already there, just not visible.










share|improve this question

























  • Is it 2013 or 2016? have you tried both versions? Maybe on different computers? Does it happen in all workbooks?

    – Máté Juhász
    Sep 25 '18 at 17:12











  • @MátéJuhász 2013 and 2016, yes tried both versions - also, tried on different computers - yes, it happens on all workbooks

    – Rand Random
    Sep 25 '18 at 17:16






  • 2





    I've tried and have the same issue, good spotting! It seems to be a bug. Actually you don't need to insert a new sheet, just changing to another sheet then back, or switching to print preview refreshes the value from the other workbook.

    – Máté Juhász
    Sep 25 '18 at 19:42











  • @MátéJuhász - thx for your comment, see my update

    – Rand Random
    Sep 26 '18 at 7:40






  • 1





    That sounds like a solution, please post it as an answer, instead of adding it to your question.

    – Máté Juhász
    Sep 26 '18 at 8:18
















4















I have the following setup:




  1. 1 Workbook named Workbook1 with the value 123 in A1

  2. 1 Workbook named Workbook2 with value =[Workbook1.xlsx]Sheet1!$A$1 in A1


Now, when I go and change the value of Workbook1 to something else the value gets updated as expected in Workbook2.



But sadly, when I minimize Workbook2 and than change the value in Workbook1 and than restore Workbook2 to verify if the value has been updated it still shows the old value.



Notice:




  • after restoring the window even if I calculate (F9) or
    calculate the whole sheet (SHIFT + F9) the value doesn't update


  • when I add a new Worksheet to Workbook2 the value gets updated (was more a coincidence that I found that)



Is there a way to fix this?



Update:



As Máté Juhász already mentioned in the comments there are various ways to actually update the value.



I myself found that switching the View triggers the cell to update or simply chaning the Display ruler, Display gridlines, Display headers property they all triggered the cell to update its value.



I also had a look at:



MsgBox Application.Range("A1").Value
MsgBox Application.Range("A1").Text


Both, output the correct value while the old value is still visible.



So a thought came up that all those operations that trigger the cell have one thing in common the screen gets updated, so my current "solution" is the following:



Private Sub Workbook_WindowResize(ByVal Wn As Window)

Dim oldValue As Boolean
oldValue = Application.ScreenUpdating
Application.ScreenUpdating = Not oldValue
Application.ScreenUpdating = oldValue

End Sub


With the knowledge of the issue just being a not updating screen, I also tried to simply scroll my cell out of the visible range and scroll back - this also did update the cell's value.



Also, now it makes sense why F9 and SHIFT + F9 didn't trigger the cell to update, since the value is already correct, there just simply isn't a thing for the calculation to update - since it's already there, just not visible.










share|improve this question

























  • Is it 2013 or 2016? have you tried both versions? Maybe on different computers? Does it happen in all workbooks?

    – Máté Juhász
    Sep 25 '18 at 17:12











  • @MátéJuhász 2013 and 2016, yes tried both versions - also, tried on different computers - yes, it happens on all workbooks

    – Rand Random
    Sep 25 '18 at 17:16






  • 2





    I've tried and have the same issue, good spotting! It seems to be a bug. Actually you don't need to insert a new sheet, just changing to another sheet then back, or switching to print preview refreshes the value from the other workbook.

    – Máté Juhász
    Sep 25 '18 at 19:42











  • @MátéJuhász - thx for your comment, see my update

    – Rand Random
    Sep 26 '18 at 7:40






  • 1





    That sounds like a solution, please post it as an answer, instead of adding it to your question.

    – Máté Juhász
    Sep 26 '18 at 8:18














4












4








4








I have the following setup:




  1. 1 Workbook named Workbook1 with the value 123 in A1

  2. 1 Workbook named Workbook2 with value =[Workbook1.xlsx]Sheet1!$A$1 in A1


Now, when I go and change the value of Workbook1 to something else the value gets updated as expected in Workbook2.



But sadly, when I minimize Workbook2 and than change the value in Workbook1 and than restore Workbook2 to verify if the value has been updated it still shows the old value.



Notice:




  • after restoring the window even if I calculate (F9) or
    calculate the whole sheet (SHIFT + F9) the value doesn't update


  • when I add a new Worksheet to Workbook2 the value gets updated (was more a coincidence that I found that)



Is there a way to fix this?



Update:



As Máté Juhász already mentioned in the comments there are various ways to actually update the value.



I myself found that switching the View triggers the cell to update or simply chaning the Display ruler, Display gridlines, Display headers property they all triggered the cell to update its value.



I also had a look at:



MsgBox Application.Range("A1").Value
MsgBox Application.Range("A1").Text


Both, output the correct value while the old value is still visible.



So a thought came up that all those operations that trigger the cell have one thing in common the screen gets updated, so my current "solution" is the following:



Private Sub Workbook_WindowResize(ByVal Wn As Window)

Dim oldValue As Boolean
oldValue = Application.ScreenUpdating
Application.ScreenUpdating = Not oldValue
Application.ScreenUpdating = oldValue

End Sub


With the knowledge of the issue just being a not updating screen, I also tried to simply scroll my cell out of the visible range and scroll back - this also did update the cell's value.



Also, now it makes sense why F9 and SHIFT + F9 didn't trigger the cell to update, since the value is already correct, there just simply isn't a thing for the calculation to update - since it's already there, just not visible.










share|improve this question
















I have the following setup:




  1. 1 Workbook named Workbook1 with the value 123 in A1

  2. 1 Workbook named Workbook2 with value =[Workbook1.xlsx]Sheet1!$A$1 in A1


Now, when I go and change the value of Workbook1 to something else the value gets updated as expected in Workbook2.



But sadly, when I minimize Workbook2 and than change the value in Workbook1 and than restore Workbook2 to verify if the value has been updated it still shows the old value.



Notice:




  • after restoring the window even if I calculate (F9) or
    calculate the whole sheet (SHIFT + F9) the value doesn't update


  • when I add a new Worksheet to Workbook2 the value gets updated (was more a coincidence that I found that)



Is there a way to fix this?



Update:



As Máté Juhász already mentioned in the comments there are various ways to actually update the value.



I myself found that switching the View triggers the cell to update or simply chaning the Display ruler, Display gridlines, Display headers property they all triggered the cell to update its value.



I also had a look at:



MsgBox Application.Range("A1").Value
MsgBox Application.Range("A1").Text


Both, output the correct value while the old value is still visible.



So a thought came up that all those operations that trigger the cell have one thing in common the screen gets updated, so my current "solution" is the following:



Private Sub Workbook_WindowResize(ByVal Wn As Window)

Dim oldValue As Boolean
oldValue = Application.ScreenUpdating
Application.ScreenUpdating = Not oldValue
Application.ScreenUpdating = oldValue

End Sub


With the knowledge of the issue just being a not updating screen, I also tried to simply scroll my cell out of the visible range and scroll back - this also did update the cell's value.



Also, now it makes sense why F9 and SHIFT + F9 didn't trigger the cell to update, since the value is already correct, there just simply isn't a thing for the calculation to update - since it's already there, just not visible.







microsoft-excel microsoft-excel-2013 microsoft-excel-2016






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Sep 26 '18 at 7:46







Rand Random

















asked Sep 25 '18 at 16:47









Rand RandomRand Random

8918




8918













  • Is it 2013 or 2016? have you tried both versions? Maybe on different computers? Does it happen in all workbooks?

    – Máté Juhász
    Sep 25 '18 at 17:12











  • @MátéJuhász 2013 and 2016, yes tried both versions - also, tried on different computers - yes, it happens on all workbooks

    – Rand Random
    Sep 25 '18 at 17:16






  • 2





    I've tried and have the same issue, good spotting! It seems to be a bug. Actually you don't need to insert a new sheet, just changing to another sheet then back, or switching to print preview refreshes the value from the other workbook.

    – Máté Juhász
    Sep 25 '18 at 19:42











  • @MátéJuhász - thx for your comment, see my update

    – Rand Random
    Sep 26 '18 at 7:40






  • 1





    That sounds like a solution, please post it as an answer, instead of adding it to your question.

    – Máté Juhász
    Sep 26 '18 at 8:18



















  • Is it 2013 or 2016? have you tried both versions? Maybe on different computers? Does it happen in all workbooks?

    – Máté Juhász
    Sep 25 '18 at 17:12











  • @MátéJuhász 2013 and 2016, yes tried both versions - also, tried on different computers - yes, it happens on all workbooks

    – Rand Random
    Sep 25 '18 at 17:16






  • 2





    I've tried and have the same issue, good spotting! It seems to be a bug. Actually you don't need to insert a new sheet, just changing to another sheet then back, or switching to print preview refreshes the value from the other workbook.

    – Máté Juhász
    Sep 25 '18 at 19:42











  • @MátéJuhász - thx for your comment, see my update

    – Rand Random
    Sep 26 '18 at 7:40






  • 1





    That sounds like a solution, please post it as an answer, instead of adding it to your question.

    – Máté Juhász
    Sep 26 '18 at 8:18

















Is it 2013 or 2016? have you tried both versions? Maybe on different computers? Does it happen in all workbooks?

– Máté Juhász
Sep 25 '18 at 17:12





Is it 2013 or 2016? have you tried both versions? Maybe on different computers? Does it happen in all workbooks?

– Máté Juhász
Sep 25 '18 at 17:12













@MátéJuhász 2013 and 2016, yes tried both versions - also, tried on different computers - yes, it happens on all workbooks

– Rand Random
Sep 25 '18 at 17:16





@MátéJuhász 2013 and 2016, yes tried both versions - also, tried on different computers - yes, it happens on all workbooks

– Rand Random
Sep 25 '18 at 17:16




2




2





I've tried and have the same issue, good spotting! It seems to be a bug. Actually you don't need to insert a new sheet, just changing to another sheet then back, or switching to print preview refreshes the value from the other workbook.

– Máté Juhász
Sep 25 '18 at 19:42





I've tried and have the same issue, good spotting! It seems to be a bug. Actually you don't need to insert a new sheet, just changing to another sheet then back, or switching to print preview refreshes the value from the other workbook.

– Máté Juhász
Sep 25 '18 at 19:42













@MátéJuhász - thx for your comment, see my update

– Rand Random
Sep 26 '18 at 7:40





@MátéJuhász - thx for your comment, see my update

– Rand Random
Sep 26 '18 at 7:40




1




1





That sounds like a solution, please post it as an answer, instead of adding it to your question.

– Máté Juhász
Sep 26 '18 at 8:18





That sounds like a solution, please post it as an answer, instead of adding it to your question.

– Máté Juhász
Sep 26 '18 at 8:18










1 Answer
1






active

oldest

votes


















0














If both files are in same folder, it gets immediately automatically updated across the files.



If both files are in different folders (in my case different disk partitions also), it doesn't get updated. To update, first save the file in which you have made the change. Then go to the other file, select the cell to Edit (pressing F2), press Enter without doing any change. It gets updated.



It may not be a bug, it may be related to "Updating external links" settings, there are so many settings of these that I could not figure out. Someone knowing about that might shed some light.






share|improve this answer























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "3"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1361290%2fexcel-2013-2016-not-updating-values-while-workbook-is-minimized%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    If both files are in same folder, it gets immediately automatically updated across the files.



    If both files are in different folders (in my case different disk partitions also), it doesn't get updated. To update, first save the file in which you have made the change. Then go to the other file, select the cell to Edit (pressing F2), press Enter without doing any change. It gets updated.



    It may not be a bug, it may be related to "Updating external links" settings, there are so many settings of these that I could not figure out. Someone knowing about that might shed some light.






    share|improve this answer




























      0














      If both files are in same folder, it gets immediately automatically updated across the files.



      If both files are in different folders (in my case different disk partitions also), it doesn't get updated. To update, first save the file in which you have made the change. Then go to the other file, select the cell to Edit (pressing F2), press Enter without doing any change. It gets updated.



      It may not be a bug, it may be related to "Updating external links" settings, there are so many settings of these that I could not figure out. Someone knowing about that might shed some light.






      share|improve this answer


























        0












        0








        0







        If both files are in same folder, it gets immediately automatically updated across the files.



        If both files are in different folders (in my case different disk partitions also), it doesn't get updated. To update, first save the file in which you have made the change. Then go to the other file, select the cell to Edit (pressing F2), press Enter without doing any change. It gets updated.



        It may not be a bug, it may be related to "Updating external links" settings, there are so many settings of these that I could not figure out. Someone knowing about that might shed some light.






        share|improve this answer













        If both files are in same folder, it gets immediately automatically updated across the files.



        If both files are in different folders (in my case different disk partitions also), it doesn't get updated. To update, first save the file in which you have made the change. Then go to the other file, select the cell to Edit (pressing F2), press Enter without doing any change. It gets updated.



        It may not be a bug, it may be related to "Updating external links" settings, there are so many settings of these that I could not figure out. Someone knowing about that might shed some light.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Feb 8 at 21:06









        VSRawatVSRawat

        15512




        15512






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Super User!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1361290%2fexcel-2013-2016-not-updating-values-while-workbook-is-minimized%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Probability when a professor distributes a quiz and homework assignment to a class of n students.

            Aardman Animations

            Are they similar matrix