Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with Xecel Sheets
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 |
#2
|
|||
|
|||
Quote:
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.
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
#3
|
|||
|
|||
Quote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to update data from multiple sheets to one specific sheets | Excel Discussion (Misc queries) | |||
copy cell info to other sheets, other sheets dont contain all row. | Excel Worksheet Functions | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
In 3 active sheets in wkbk, determine& display the # of sheets that have data | Excel Discussion (Misc queries) |