Copying to another spreadsheet

User avatar
weese237
2StarLounger
Posts: 189
Joined: 26 Jan 2010, 23:39
Location: Florida, USA

Copying to another spreadsheet

Post by weese237 »

Hi,
I may not explain this correctly, but I'll give it my best shot.

I have two spreadsheets that are basically the same. One is a template that was developed at the Corporate office. I took this template and changed it a little bit to suit our needs better by adding a "notes" column to about six worksheets. Everything worked fine last year.

They (Corporate) updated two tabs of the original template for 2010. (It has about 15 tabs (worksheets) in the spreadsheet.) I copied and pasted those two revised tabs from their template into my altered template. Everything was working fine until this month. After some investigation I figured out what the problem was but I don't know how to fix it.

The templates have a ton of formulas, internal and external links. When I copied the information from one spreadsheet to another, for some reason the formulas in the second template now includes the original template's file name.

For example:
Original Template: =HLOOKUP(Setup!$B$4,Forecast!$E$3:$P$1937,Forecast!A8,FALSE)

Second template:=HLOOKUP('[801 Checkbook Template.xls]Setup'!$B$4,'[801 Checkbook Template.xls]Forecast'!$E$3:$P$1937,'[801 Checkbook Template.xls]Forecast'!A8,FALSE)

As you can see, the file name has inserted itself. I need them be exactly the same. How do I do that when pasting? I looked at "Paste Special" but didn't see what could work. :hairout:

Thanks!
Louise     :smile:

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copying to another spreadsheet

Post by HansV »

If you copy a worksheet to another workbook, the only way to prevent formulas from referring to the original workbook is to copy the worksheet and all sheets it refers to in one go, at the same time (not one by one).

You can repair your formulas by using Replace. In your example, your own template should contain worksheets Setup and Forecast, otherwise the modified formulas won't work any more.
  • Make a backup copy of your template first.
  • Select Edit | Replace (or in Excel 2007 Find & Select > Replace on the Home tab of the Ribbon).
  • Click Options >>.
  • From the Within dropdown, select Workbook.
  • Make sure that "Match entire cell contents" is clear (not ticked), and "Match case" as well.
  • Enter [801 Checkbook Template.xls] in the "Find what" box.
  • Leave the "Replace with" box empty.
  • Click "Replace All".
This should change the external references to references within your template.
Best wishes,
Hans

User avatar
weese237
2StarLounger
Posts: 189
Joined: 26 Jan 2010, 23:39
Location: Florida, USA

Re: Copying to another spreadsheet

Post by weese237 »

Thanks Hans! Forgot about find & replace. Great idea! Thanks so much!

Now another question: I just noticed on the second template that it also entered some (')s. I assume I get rid of those too? or will just getting rid of the file name take care of those too?
Louise     :smile:

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copying to another spreadsheet

Post by HansV »

The apostrophes around sheet names are used if it's an external reference and/or the sheet name contains spaces. They should disappear automatically when you find & replace in your example, since the sheet names don't contain spaces. You don't have to remove them yourself.
Best wishes,
Hans

User avatar
weese237
2StarLounger
Posts: 189
Joined: 26 Jan 2010, 23:39
Location: Florida, USA

Re: Copying to another spreadsheet

Post by weese237 »

Great! Thanks again! :trophy:
Have a good evening / morning? :laugh:
Louise     :smile: