VBA : How to link data from 1 worksheet to the other?

  • Hi all.


    can someone please teach me how to link data values in 1 worksheet to the other worksheet in a same workbook.


    If possible, it will automatically be updated in both worksheets.


    To be more precise, if the user enters the value of A in a cell in sheet1, then when the user goes to sheet 2, the defined cell will show the updated value of A.



    Thx.. waiting for a reply.

  • In sheet 2


    Select the cell where you want the data to appear.


    Now select sheet 1


    Now select the cell in sheet 1 that you want to appear in sheet 2


    Hit enter.

  • Hi Dave,


    Highlight the cell in Sheet 2 that you wish to display the value from Sheet 1.


    Press + to initiate a formula.


    Navigate to and highlight the cell in Sheet 1 containing the value you wish to display on Sheet 2.


    Press Enter.


    In the cell on Sheet 2 you should now see a formula similar to this:


    =+Sheet1!A1


    where "Sheet1" is the name of the worksheet containing the desired value and "A1" trhe location of the cell containing the desired value.


    Hope that helps.


    Rich

    If I've been helpful, let me know. If I haven't, let me know that too. 

  • I applied the selections as stated, however, when I update the cell and go to the sheet with the link, it is not updated. And it doesn't update until I save the workbook.
    What do I need to do to get the automatic update on entry?


    Thanks
    Never mind.. I found that if I went to "Tools", "Options" and check the "Calculations" it works. Somehow that must have been turned off.


    Or


    Is there something else that I should look at?:biggrin:

    Skillful Plagiarism Beats Inept Creativity

  • RHerb,
    You have it correct. If calcualtions are set to Manual then the workbook only updates when you press F9 or (if the options are set for it) when you save. Setting Calculation to Automatic will make the links update dynamically and is noramlly the way to go until you have a very big (and slow) workbook.


    P.S. You also can use the "=" sign (without quotes) when putting in a formula. The "+" and "=" are equivalent; it's a matter of preference.


    Note you also can type in the referrences to other sheets directly if you prefer.


    e.g.,
    =Sheet1!A1
    will set the cell containing this formula to the value of cell A1 on Sheet1. Click and point is often easier.

  • Thomach:
    Thanks! I did notice that once I turned the automatic back on I could once again copy the "source" cell and paste-special on the desitnation cell. I had the formula you indicated as : =Sheet1!A1

    Skillful Plagiarism Beats Inept Creativity

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!