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


I

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
 
Ad

Advertisements

D

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
 
S

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.
 
Ad

Advertisements

I

Ivan

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

Ivan

Shane Devenshire said:
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


Ivan said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top