Keep the exact same workSHEET, in two seperate workBOOKS.

  • Thread starter Thread starter beeawwb
  • Start date Start date
B

beeawwb

This is probably something really easy, but I don't know how to do it.

I currently maintain 2 different worksheets for statistical purposes. 1
for my department, which keeps data on a daily, weekly, fortnightly,
and finally yearly (by week) basis. This is MailStats.xls.

I also maintain the yearly (by week) version for the other 2
departments, as well as the summaries. This is WorkStats.xls

Now, the yearly (by week) sheet is the same in both workbooks. I simply
finished off designing WorkStats.xls and copied my departments
worksheet (SGIC_GIS) to MailStats as SGIC_GIS_2004. The data going into
this sheet is exactly the same at all times.

Is there a way so that when I enter data into
MailStats.xls[SGIC_GIS_2004], the data goes into
WorkStats.xls[SGIC_GIS] AND, vice versa. So, I can enter data into
either worksheet, and have it display exactly the same, when I open up
the other workbook. Or, if I have both workbooks open at the same time,
any changes I make to one sheet, show up in the other.

Sorry if I am confusing anybody with my references, let me know if you
need more information on my problem.

Thanks for the help.

-Bob
 
beeawwb > said:
Is there a way so that when I enter data into
MailStats.xls[SGIC_GIS_2004], the data goes into
WorkStats.xls[SGIC_GIS] AND, vice versa. So, I can enter data into
either worksheet, and have it display exactly the same, when I open up
the other workbook. Or, if I have both workbooks open at the same time,
any changes I make to one sheet, show up in the other.
Click to expand...

You may be able to do this with Worksheet_Change event handlers in both
workbooks for the common worksheet, but it'd be lots simpler to make that
common worksheet a separate workbook on its own, and reference it through
external link references from the other two workbooks. That way you'd be
making changes in one and only one place and not have to bother with
synchronization.
 
The problem with making it a seperate workbook is that I need to have
access to the current data in both sheets.

The data for weekly stats is compiled from data on daily stats, and
then put into a yearly statistics calander and dynamic chart. It is
then placed into the department total sheet.

What could be done, is make it so that when I change data in the GIS
sheet (MailStats.xls) it changes in WorkStats.xls and not vice versa.
It would be rare that I would be entering data into SGIC_Gis on
WorkStats that wasn't already contained in SGIC_Gis_2004.

Is there a way to do that? I'm beginning to wonder if it's just simpler
to enter the data twice, as I have been doing, maintaining both
spreadsheets serperately.

Thanks,

Robert.
 
beeawwb > said:
The problem with making it a seperate workbook is that I need to have
access to the current data in both sheets.
Click to expand...
....

And how would you lack such access if both current workbooks accessed the
same data in the proposed third workbook? You'd perform data entry in this
new workbook, then reference that data via external link references rather
than references to other worksheets in the same workbook in the two current
workbooks. Single centralized data sources are always more robust and
reliable than synchronizing multiple instances of what should be identical
data. The only overriding reason for multiple instances is the need for
speed when users are geograpically dispersed. This didn't appear to be the
case in your situation.
Is there a way to do that? I'm beginning to wonder if it's just simpler
to enter the data twice, as I have been doing, maintaining both
spreadsheets serperately.
Click to expand...

While it may be expedient to enter data twice, it's dangerous in terms of
impaired data integrity. How much VBA would you want to use? You can't do
this automatically without VBA. Even using VBA, your choices would be to
update the other workbook upon entry in one workbook (slow) or batch
synchronize data when entry is completed (less reliable).
 
Now that I think about it, why do I even need a copy of the departmen
data in my local copy.

*thinks*

Now I remember. I put a copy in my local book as a method of keepin
records for the department, because the main copy was passwor
protected and maintained by the Information Technology division. I the
discovered a number of errors and rewrote the entire spreadsheet fro
scratch, basing it on the original, but making substantia
improvements. Now, I maintain the sheet, and no longer need a print ou
to hand over so it can update the main workbook. I can just enter th
data straight into it.

Many thanks for your help, sorry I'm being such a drain / confusing
All I needed to do was think about the problem and realize how I coul
fix it. Delete my copy. It's redundant and no longer needed.

Thanks again.

-Bo
 
Back
Top