Copy a sheet contained formulas from one workbook to another and remove file links

[Ivan]

Hello,

in our company we use a special excel file with more sheets. Every user
copies first this file from the server to his own computer and then he/she
can customize the workbook. Usually the user only makes his own new sheets
where there are formulas which contain cells from the original sheets.

Occasionally a new release of the original excel file appears and the user
must make a new copy of this file. Commonly the user wants to have in the
new file all of his own created sheets from the first workbook. If he
normally copies the sheets from the first workbook to the new one, the
formulas to his regret contain the links to the cells (sheets) of the first
workbook instead to the cells (sheets) of the new one.

For better imagination - the user wants in his Sheet2 of a Book2 a formula
like

=Sheet1!A1+Sheet1!A2 ,

he gets but a formula like

=[Book1]Sheet1!A1+[Book1]Sheet1!A2 .

One solution is that he can replace all [Book1] with an empty string. Is
there any other "paste special" way of copying?

Ivan

 

[Dave Peterson]

You could use
Edit|links|change links
(xl2003 menus)

or the edit|replace that you suggested.

Another option would be to create a macro that provides the user with a choice
of what worksheets and then does the copy (and either avoids the problem or
fixes the problem).

Hello,

in our company we use a special excel file with more sheets. Every user
copies first this file from the server to his own computer and then he/she
can customize the workbook. Usually the user only makes his own new sheets
where there are formulas which contain cells from the original sheets.

Occasionally a new release of the original excel file appears and the user
must make a new copy of this file. Commonly the user wants to have in the
new file all of his own created sheets from the first workbook. If he
normally copies the sheets from the first workbook to the new one, the
formulas to his regret contain the links to the cells (sheets) of the first
workbook instead to the cells (sheets) of the new one.

For better imagination - the user wants in his Sheet2 of a Book2 a formula
like

=Sheet1!A1+Sheet1!A2 ,

he gets but a formula like

=[Book1]Sheet1!A1+[Book1]Sheet1!A2 .

One solution is that he can replace all [Book1] with an empty string. Is
there any other "paste special" way of copying?

Ivan

 

[Shane Devenshire]

Hi,

In Excel 2007 you could choose Office Button, Prepare, Edit Links to Files,
select the file with the links and choose Change Source, navigate to your
current file select it and click Open.

 

[Ivan]

Thank you Shane. (We are using Excel 2007) I knew there must be something
like this.

Ivan

Hi,

In Excel 2007 you could choose Office Button, Prepare, Edit Links to
Files,
select the file with the links and choose Change Source, navigate to your
current file select it and click Open.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Hello,

in our company we use a special excel file with more sheets. Every user
copies first this file from the server to his own computer and then
he/she
can customize the workbook. Usually the user only makes his own new
sheets
where there are formulas which contain cells from the original sheets.

Occasionally a new release of the original excel file appears and the
user
must make a new copy of this file. Commonly the user wants to have in the
new file all of his own created sheets from the first workbook. If he
normally copies the sheets from the first workbook to the new one, the
formulas to his regret contain the links to the cells (sheets) of the
first
workbook instead to the cells (sheets) of the new one.

For better imagination - the user wants in his Sheet2 of a Book2 a
formula
like

=Sheet1!A1+Sheet1!A2 ,

he gets but a formula like

=[Book1]Sheet1!A1+[Book1]Sheet1!A2 .

One solution is that he can replace all [Book1] with an empty string.
Is
there any other "paste special" way of copying?

Ivan