Copy and Update data in 2nd workbook from 1st work book

Hi,

I have a work book 1 with all my business sales data. I want to make another workbook, which has exact same data from workbook 1. I want that happen automatically copied on work book 2 from work book 1. Also when I update data on workbook 1, my work book 2 data also updates.

my work book 1 has some formula work.

I want this way becuase I don't want to do copy and paste everytime. I frequently updates data on my work book 1.

And make sure it is 2 different workbooks, when you ppl help me.

I hope this understands you guys.

please let me know if you have any questions.

Thanks

Hi,

Upload representative examples of your two workbooks and create the results you expect to see and add some notes saying how you have arrived at the results and no doubt we can then help.

Rgds

Ooopps!

There doesn't seem to be any file attached?

Edit: Ooopps - sorry, my mistake, I didn't spot that the first post had been edited to have the file attached.

Rob

hi Top1,

Originally Posted by top1

I have a work book 1 with all my business sales data. I want to make another workbook, which has exact same data from workbook 1.

...

And make sure it is 2 different workbooks, when you ppl help me.

I'm a strong supporter of having "a single version of the truth". The more times information is duplicated, the greater the chance of someone relying on out of date data.
I strongly suggest only using a single file & using some sort of backup procedure to ensure that you have a "backup copy" of the file.

Can you please explain why you want a second file that is exactly the same as the first?

If your file only consists of a single monthly sheet (with a constant/static layout) & you really want a second file to mirror its data then:

1) I think the simplest solution is:
- Open both workbooks
- In cell A1 of workbook 2 type "="
- then, using your mouse, click on cell A1 of workbook 1 & press [enter]
- this will give you an equation similar to
"='[Work book 1.xls]Sheet1'!$A$1:$F$1"
- Now edit the formula to change it from the above to:
"='[Work book 1.xls]Sheet1'!A1"
- Copy this formula down and across as many rows (of the sheet in workbook 2) as necessary to cover the range of cells in workbook 1 that will have information in them.

2) Create a pivot table in workbook 2 & set its source data to be the sheet in workbook 1.


Rob

hi Top1,

Yes, I can help with your last two issues. However, I find it easier to provide the "best" (always a subjective phrase ) solutions when I understand the context of the problem.

Originally Posted by broro183

...
Can you please explain why you want a second file that is exactly the same as the first?
...

Thanks
Rob

hi Top1,

Sorry about the slow response. Provided that the formatting in the first workbook stays constant, you could:
1) open both spreadsheets in the same instance of excel.
2) click the grey square (or triangle) in the top left of the workbook1 spreadsheet (above the list of row numbers & to the left of the list of column letters). This will select all the cells on the sheet. Or, an alternative would be to select cell A1, then press [ctrl + shift + end], which selects all the cells that excel considers as "used".
3) press [ctrl + c] to copy.
4) select cell A1 in workbook2, press [alt + e + s + w] - [enter] to copy the column widths, then [alt + e + s + t] - [enter] to copy the formats & then press [esc] to deactivate the clipboard.

However, I strongly recommend you change to only have one copy of the data. If you want to keep the pivot table in a separate file, you can link the Data Source (via the pivot table wizard) of the pivot table in workbook2 to the original data in workbook1.

hth
Rob