You are here:

Excel/Excel Automatic Update From One workbook to another workbook in excel

Advertisement


Question
QUESTION: I have one Excel Question,
i have one excel file in location C:UsersChandraDesktopMain File.Xlsx and this is my main file. All information include in this file for eg: Main File.Xlsx, Sheet Name "Detail"
And i have one summery File C:UsersChandraDesktopSummery File.Xlsx. Summery Sheet
what i want when i update something "Main File, Detail Sheet" it need to be update automatic in "Summery File, Summery Sheet". but i need only A, B, C, F, N column value from "Main file, Detail Sheet" need be update in "Summery File, Summery Sheet A, B, C, D, E.
i dont know my question is clear or not

Please Help Me, I hope someone has this answer.
Thank in advance.

ANSWER: The easiest method would be to link the cells - so on the summary file, summary sheet you would have a formula that looked something like

=[Summery File.xlsx]'Summery Sheet'!A2

You can easily create these links by having both files open.  Type the = sign and then click onto the other workbook, worksheet and cell - this will give you the formula but with Absolute References (the dollar signs) - remove these and you will then have a formula you can drag down and across the affected rows, though you would need to amend the reference for F/D and N/E as these aren't the same columns as each other!

You would need this formula to be on all cells that needed to update - whenever you open the summary file, you can get the updated data.  You COULD use VBA to update the file without formulas, but this is probably overkill.


---------- FOLLOW-UP ----------

QUESTION: I know that way, Formula i know, i could use Vlookup or match, or direct link with cell (=), but i dont wannna drag or do anything in my summery file. once a month i open summery file, i wanna get summery from MAIN File without do noting.
I Hope you have vba code also.
thank you.

ANSWER: something along these lines will work

Sub tester()
Dim CurBook As Workbook, MyBook As Workbook
Set CurBook = ActiveWorkbook
Set MyBook = Workbooks.Open("c:\Users\Chandra\Desktop\Main File.Xlsx")
With MyBook
   CurBook.Sheets("Sheet1").Range("a:a").Value = .Sheets("Sheet1").Range("a:a").Value
   CurBook.Sheets("Sheet1").Range("b:b").Value = .Sheets("Sheet1").Range("b:b").Value    
   CurBook.Sheets("Sheet1").Range("c:c").Value = .Sheets("Sheet1").Range("c:c").Value
   CurBook.Sheets("Sheet1").Range("f:f").Value = .Sheets("Sheet1").Range("d:d").Value
   CurBook.Sheets("Sheet1").Range("n:n").Value = .Sheets("Sheet1").Range("e:e").Value
   .Close True

End With
End Sub

At the moment this is set as a standalone macro - to run it when you open the summary sheet,
you would need to have it running on the OPEN event of the workbook

---------- FOLLOW-UP ----------

QUESTION: Thank For this Help Mr AIDAN,
I tried this code, it doing something,and open my main file also.
but not updating value in summery, here is my rough file, can u please check it once. what wrong with my file
http://speedy.sh/ZFj8X/Rough-File.rar
file name is different here. i change file name also.
but nothing happen.

thank you. hoping quick response

Answer
I have set it to run from the Active Workbook, which means it needs to be the summary file that is active when you run it.  I will take a look at the rough file when I get home (cannot access file share sites from current location) and update this reply at that point.

''''''''''''''''''''''''''''''''''''''''''''''''''''

When I go to the site to download the file it will only let me do it via an Exe file - which I'm not prepared to do as these are not files I can trust.  If it helps, my direct email is aidan.heritage@virgin.net - you can send the file direct to me that way?
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.