Worksheet management

[anon]

I'm struggling with worksheet and workbook management. I'm hopeful
that someobody has a solution that I can't seem to come up with. I've
searched and can't find any mention of this particular issue.

I have a workbook with 10 worksheets in it. I have 2 copies of this
workbook (one for each of my two clients ;-)). Worksheets 2 through
10 are identical in both workbooks. Worksheet 1 has the "data" for
the individual client.

The formulas in worksheets 2 through 10 reference the data on
worksheet 1, as well as varioious other cells on other worksheets.
That is, some of the worksheets will not only refer to worksheet 1,
but, for example, worksheet 5 might also pull some information from
worksheet 2.

So far, so good.

But let's say that I make a whole bunch of updates to worksheet 2 (one
of the worksheets that does "work", as opposed to worksheet 1 which is
the "data input" worksheet) in the first of my two workbooks. I now
want to make those same updates to worksheet 2 of my second workbook.

Is there a "most efficient" way to do this?

I have tried copying worksheet 2 from workbook 1 into workbook 2 and
then deleting the old worksheet 2 from workbook2 and then renaming
"worksheet 2 (2)" back to "worksheet 2". It doesn't work because it
leaves all of the formulas in worksheets 3 through 10 in tatters, to
the extent that those workbooks refer to "worksheet 2" as it is trying
to refer to a worksheet that was deleted, thereby giving me heaps of
"REF" errors.

The same kind of problem takes place if I reverse my solution and
attempt to make a new copy of workbook 1 (which would then be named
workbook 3) and then copy the data worksheet from workbook 2 into
workbook 3, deleting the old data worksheet and renaming the newly
copied in data worksheet. This is actually worse, because every
single one of the "work" worksheets gets platered with REF errors.

I hope this explains my problem.

Thanks for your time.

Jim

 

[Tyla]

Jim,

One thing you might try after copying "Sheet2" to the new workbook and
deleting the original "Sheet2" there is to break the links between the
two workbooks, a common source of the REF error. To do this in Excel
2003, go to "Edit / Links" then hit the "Change Source" button and
choose the new (current) workbook.

/ Tyla /

 

[anon]

Jim,

One thing you might try after copying "Sheet2" to the new workbook and
deleting the original "Sheet2" there is to break the links between the
two workbooks, a common source of the REF error. To do this in Excel
2003, go to "Edit / Links" then hit the "Change Source" button and
choose the new (current) workbook.

/ Tyla /

Tyla,

Thanks. While it doesn't do exactly what I want, I can modify my
approach and it will do just fine. If I split the DATA from the LOGIC
into separate workborks, then I can maintain a single LOGIC workbook
and replace the DATA workbook with the trick that you described. Once
the DATA is segregated from the LOGIC, I can update the LOGIC workbook
at will (with appropriate versioning, etc.).

Many thanks.

Jim