Copy worksheet to new doc w/formulas not referencing previous doc

F

Feolet20

We created a new worksheet tab that we want to add to all of our previous
worksheets. Is there a way to copy the worksheet (with formulas) into the
new document without the formulas referencing the previous one?

All the spreadsheets have the same tabs, and formulas within these tabs
reference other parts.

We've tried copy/paste, paste special formulas, copying the whole tab by
right clicking, and drag and drop with both windows open. All of these still
reference the previous document, giving the formula

=MIN('[Old Spreadsheet.xls]multiplot'!N4732:N9128)

We want it to reference the multiplot tab within the new document, not the
tab from the previous document.

We're using Excel 2003.

Any help is appreciated, thanks.
 
F

Feolet20

I just found this:

With the sheet selected, go to Edit > Go to > Special > Select formulas,
then Edit > Find insert [Old Spreadsheet.xls] click replace tab, Replace all

which theorectically would work by removing the reference, however when I
click replace all it takes me to an Update Values: multiplot window with
files to select? Then I can't get out of it by clicking cancel, I have to go
to task manager and end program. Help? Is this the right direction to go in?
 
G

Gord Dibben

One reliable method................

In source sheet edit>replace

What: =

With: ^^^

Replace all.

Copy and paste to new workbook.

Reverse the edit>replace in both workbooks or just in destination then close
the source WB without saving changes.


Gord Dibben MS Excel MVP
 
F

Feolet20

How do I get to the source sheet in Excel 2003?

Gord Dibben said:
One reliable method................

In source sheet edit>replace

What: =

With: ^^^

Replace all.

Copy and paste to new workbook.

Reverse the edit>replace in both workbooks or just in destination then close
the source WB without saving changes.


Gord Dibben MS Excel MVP

We created a new worksheet tab that we want to add to all of our previous
worksheets. Is there a way to copy the worksheet (with formulas) into the
new document without the formulas referencing the previous one?

All the spreadsheets have the same tabs, and formulas within these tabs
reference other parts.

We've tried copy/paste, paste special formulas, copying the whole tab by
right clicking, and drag and drop with both windows open. All of these still
reference the previous document, giving the formula

=MIN('[Old Spreadsheet.xls]multiplot'!N4732:N9128)

We want it to reference the multiplot tab within the new document, not the
tab from the previous document.

We're using Excel 2003.

Any help is appreciated, thanks.
 
G

Gord Dibben

The source sheet is the worksheet you want to copy to somewhere.



Gord Dibben MS Excel MVP


How do I get to the source sheet in Excel 2003?

Gord Dibben said:
One reliable method................

In source sheet edit>replace

What: =

With: ^^^

Replace all.

Copy and paste to new workbook.

Reverse the edit>replace in both workbooks or just in destination then close
the source WB without saving changes.


Gord Dibben MS Excel MVP

We created a new worksheet tab that we want to add to all of our previous
worksheets. Is there a way to copy the worksheet (with formulas) into the
new document without the formulas referencing the previous one?

All the spreadsheets have the same tabs, and formulas within these tabs
reference other parts.

We've tried copy/paste, paste special formulas, copying the whole tab by
right clicking, and drag and drop with both windows open. All of these still
reference the previous document, giving the formula

=MIN('[Old Spreadsheet.xls]multiplot'!N4732:N9128)

We want it to reference the multiplot tab within the new document, not the
tab from the previous document.

We're using Excel 2003.

Any help is appreciated, thanks.
 

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