[Markzas]

Hello,

I am trying to copy a value of one cell from one spreadsheet to a cell of another spreadsheet. and below is what i have comeup with so far. Can someone help or show me a better way?



i have two speadsheets.

the first sheet contains a total column
EX: in C4
=SUM(E4+G4+I4+K4+M4)

Value of C4 = (339816)

----------------------------------------------------

on the second sheet i would like the values
i have the following coded:

=SUM('C:\Free Space\[SYC.xls]ECC'!C4)

but when i update (sheet1) C4 the value in Sheet2(G3) is not updated

until, I save both sheets and reopen them.


How can:


i would like the of C4 from sheet one placed on sheet two
G3.

how ever if the value of C4 (sheet1) is altered i would like the
value in G3(sheet2) to reflect the chage immediately.


Can someone help me.

Thank you,
Mark

[Mazzaropi]

Originally Posted by Markzas

Hello,

I am trying to copy a value of one cell from one spreadsheet to a cell of another spreadsheet. and below is what i have comeup with so far. Can someone help or show me a better way?
i have two speadsheets.
the first sheet contains a total column
EX: in C4
=SUM(E4+G4+I4+K4+M4)
Value of C4 = (339816)
----------------------------------------------------
on the second sheet i would like the values
i have the following coded:
=SUM('C:\Free Space\[SYC.xls]ECC'!C4)
but when i update (sheet1) C4 the value in Sheet2(G3) is not updated
until, I save both sheets and reopen them.
How can:
i would like the of C4 from sheet one placed on sheet two
G3.
how ever if the value of C4 (sheet1) is altered i would like the
value in G3(sheet2) to reflect the chage immediately.
Can someone help me.

Thank you,
Mark

------------------------------------------------------------------------

Dear Markzas, Good Afternoon.

If you want just the G3 cell at a sheet2, to have the SAME value of a C4 cell at a C:\Free Space\[SYC.xls]ECC'! (sheet1) you just need to DO:

G3 (sheet2) = ´C:\Free Space\[SYC.xls]ECC'!C4

The value at Sheet2 is updated immediately every time the value is changed at sheet1.


Tell me if it worked for you.

[tarquinious]

Originally Posted by Markzas

Hello,

I am trying to copy a value of one cell from one spreadsheet to a cell of another spreadsheet.

From what I gather, you have links between two separate spreadsheet files, and you can't see the updates appearing in the second until you save and re-open the second?

Spreadsheet#1 = master spreadsheet
Spreadsheet#2 = links to values in Spreadsheet#1

Excel appears (from what I can see) to only update the links when you hit Save, hence the problem you can see.

One thing I can think of to help you is to put a small line of code into Spreadsheet#2 so that any links are updated the moment you open it, instead of waiting for you to save it.

In Spreadsheet#2, you will need to:
- Go into Macros (Tools/Macros in Excel 2003, Developer/Visual Basic in Excel 2007 - if Developer menu doesn't show, click the round button top left, click Excel Options, and under Popular, tick the Show Developer tab in the Ribbon option)
- Under VBAProject(Spreadsheet#2.xls), double-click on ThisWorkbook
- Drop down the list where it says (general) and select Workbook
- By default it should select Open in the right-hand drop down and give you a Private Sub Workbook_Open()
- Between the Private Sub Workbook_Open() and the End Sub, copy and paste in the following line:
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources

Congratulations! You've written a macro. Now save Spreadsheet#2 and close it. Make changes to Spreadsheet#1 then save and close.

When you open Spreadsheet#2 with the macro in it (assuming macros are enabled) it should automatically update the links for you.