is it possible to have 2 copies of identical workbooks, that will automatically update each other if I enter data into either of them

Hi,

I would like to know if it is possible to have 2 copies of identical workbooks, that will automatically update each other if I enter data into either of them. Basically, I need two identical files, but each week as I add new data, I would like it to automatically update the 'backup' file so that I only have to enter the data once.

Thanks

-------------Problems Reply------------

In a typical scenario, you'd start with one workbook and make an exact copy of it simply by copying the file and giving it a new name. Then you'd decide which one of the workbooks is going to be the master and lets say that every time you make a change in the master you want it to show up in the copy. Over in the copy workbook, you'd set up a link to cells where you can have user input in the master.

So you want the value in Sheet1 cell A1 of the copy to reflect any change in that same cell on Sheet1 of the master, so you'd have a formula (this can actually all be done through the keyboard) in the COPY book, Sheet1, cell A1 that looks something like this:

=[MasterBook.xlsx]Sheet1!$A$1

The problem here is that you can't put a similar formula over in the MasterBook, or you wouldn't be able to enter data into it - you can have a formula or a value, but not both at the same time.

We COULD set up a run-on-user-demand macro in one of the workbooks that would save the current workbook with a constant name whenever the user decides the work needs to be saved over the previous copy. Takes a button or text box on a sheet along with some code to:

Save the current workbook with the copy's name, overwriting the previous version of that copy, then

Turn right around and save itself again using the original name it had so that you continue to work in the "master" workbook. At that point you can undo any temporary "what if" entries and then save the master in whatever condition you want without automatically updating that other copy.

All of that is the code equivalent of:

File --> Save As: give new name and save it, then

File --> Save As again, but giving it the same name you started with.

The code would be provided with the name for the copy, it can figure out the original name on its own, and since this doesn't seem to be an actual archive backup copy of the data, both files can easily be placed in the same location (folder).

You wouldn't be able to link two workbooks to one another so that changes made in one would appear in the other - you could set two books up so that one is always updated from the other - but this kind of defeats the purpose of one of them being a backup because if the 'master' became corrupt, the data in the backup could become corrupted.

What you could do if the location(s) of the files is on the same computer or two computers on your local area network is to have a macro that runs when you close either workbook that would save a copy of itself under the other's name and location. If they are in the same folder together it is easiest, but not the safest. Your backup should always at least be in another folder, better yet on a different drive and best of all - on another computer.

Would you like to try something like this?

Thanks for replying first of all... If I tried to set it up with the macro, would it save a new 'duplicate' every time I amended the original document...I dont really want to end up with multiple dated copies, just one!

How would i be able to set up two books, one updating from the other?

My boss is asking the question, and she is updating a spreadsheet which now has new charges since the beginning of the tax year. Her boss is asking for a comparison of the return at the two different rates, and so far she's been inputting everything in by hand, I said that there must be an easier way of copying everything across, and then seeing the effect the different charges have on the outcome. The Higher charges must be kept separately, and cannot be on the original workbook, hence the problem!

In a typical scenario, you'd start with one workbook and make an exact copy of it simply by copying the file and giving it a new name. Then you'd decide which one of the workbooks is going to be the master and lets say that every time you make a change in the master you want it to show up in the copy. Over in the copy workbook, you'd set up a link to cells where you can have user input in the master.

So you want the value in Sheet1 cell A1 of the copy to reflect any change in that same cell on Sheet1 of the master, so you'd have a formula (this can actually all be done through the keyboard) in the COPY book, Sheet1, cell A1 that looks something like this:

=[MasterBook.xlsx]Sheet1!$A$1

The problem here is that you can't put a similar formula over in the MasterBook, or you wouldn't be able to enter data into it - you can have a formula or a value, but not both at the same time.

We COULD set up a run-on-user-demand macro in one of the workbooks that would save the current workbook with a constant name whenever the user decides the work needs to be saved over the previous copy. Takes a button or text box on a sheet along with some code to:

Save the current workbook with the copy's name, overwriting the previous version of that copy, then

Turn right around and save itself again using the original name it had so that you continue to work in the "master" workbook. At that point you can undo any temporary "what if" entries and then save the master in whatever condition you want without automatically updating that other copy.

All of that is the code equivalent of:

File --> Save As: give new name and save it, then

File --> Save As again, but giving it the same name you started with.

The code would be provided with the name for the copy, it can figure out the original name on its own, and since this doesn't seem to be an actual archive backup copy of the data, both files can easily be placed in the same location (folder).

Category:Office Views:7 Time:2013-07-01

Related post

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.075 (s). 11 q(s)