jump to navigation

How to Copy a Sheet to Other File February 12, 2010

Posted by Admin in Tutorials, Tips & Tricks.
Tags: , , , ,
trackback

Sometimes we have a complete file with data and formulas. Then we have other file with different data but the same structure. We want to copy a sheet with formula in the former file to the later, so we don’t need to rewrite the formula (because actually the formula will be the same), moreover if there are a lot of formula there. But the problem is, when we do that, the formulas is not linked to the later file as we want but still linked to the former one. Look at the example below.

In the former file:

=SUM(Dummy!A1:A5)

After we copy to the later file the formula will be:

=SUM(‘[Sales-Jakarta.xls]Dummy’!A1:A5)

So we are failed to get the same formula because the formula we get still linked to the former file (Sales-Jakarta.xls).

The tips to fix this problem is quite easy actually. After doing copy and paste operation, what you need to do is just doing Find and Replace operation (Click Ctrl+h). In Find what input type [Sales-Jakarta.xls] while in Replace with input just keep it empty. Click Replace All. Now all formulas you have is linked to the current file which is the later one. =SUM(Dummy!A1:A5).

Hopefully helpful.

*Actually you need to check whether you have more data of more formulas. If you have more formulas than the data you just need to copy the data from later file to the former (complete file with data and formula) and save as it. But if you have more data the above tips will work better.

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: