linking worksheets

[kagh78]

I am trying to copy a worksheet in its entirity into a new workbook. I would
like the two worksheets to be linked so that when the source worksheet is
updated, this feeds through to the destination document. I know this is
possible through right clicking over the worksheet tab and copying into a new
work book. It is possible on my computer at home and at my previous empolyers
and friends I have discussed it with have also confirmed this.

I have moved into a new job where the version of Excel is 2003 however the
method I have explained above does not result in the same outcome. The values
are returned but the links are not present unless one types in '='[table
a.xls]Sheet1'!$A$4'. If the cell is empty the value '0' is returned. I know
through the options tool bar this is rectifiable, however it is a much less
straightforward route, particularly when I know it is possible through
copying a worksheet. This obviously saves copying forumlas, formats and
switching off zero values.

No-one in the organisation seems to be able to help, including IT!

 

[Dave O]

Let me repeat this back, to make sure I understand: you have a
workbook with (let's just call it) Sheet A and Sheet B- let's call
this the Original. You want to copy Sheet A from Original into another
file, let's call it New. Sheet A refers to Sheet B, and should refer
only to Sheet B within the local file, not in an external file. Is
that correct, or pretty close?

I use XL 03 as well, and I have never seen a tab moved in its entirety
that does not refer back to the originating file with all that [table
a.xls]!Sheet1! business. As a workaround to solve your immediate
problem, tho, you can try this: just prior to copying the tab into the
New file, highlight all cells (with the button in the upper left
corner) and do a search and replace. Search for the = sign and replace
with a string of letters such as xyz. This converts dynamic formulas
into static text strings. Copy the tab into the New file, then
highlight the tab within the New file and do another search/replace,
this time searching for xyz and replace with the = sign.

Will that get you where you need to go?
Dave O

 

[challa prabhu]

Hi,

It is recommended that you right-click on the sheet tab and move or copy the
worksheet to a new workbook, so that all the cell width, formatting and
formulas retain their properties.

Note: If you copy the Worskeet content, to a new worksheet, the default
property of the new workshet will be applied to the new content. What will
happen is that the text wrap property of the text control would be applied to
all the cells within the worksheet.

Important; It would be a waste of time redoing the whole thing again, when
you can reuse your worksheet by moving or coping. You might have to just
re-establish the refrences and link if the situation demands.

Challa Prabhu

 

[kagh78]

Hi Dave

Thanks for the feedback. You almost had it but not quite:

Original workbook has one sheet (sheet a). I copy sheet a from original into
another file called new. New should refer to sheet a automatically and be an
exact duplicate. Depending on who I wanted to view the new document I would
hide rows / cells and save into a public location. I might make a number of
copies of the original sheet which have different views for different people.

I would only ever need to update the original workbook sheet a with any
changes and these would be reflected in all the 'new' copies of this sheet.
But when opened the 'new' copies would ask if you wanted the file to be
updated.

This doesn't happen where I work and I know it is something that usually
does.