View Full Version : "XL" Query

How do I copy only a formula from one workbook to another without any references to the workbook it’s copied from?

I thought paste special, formula only, would do it but unfortunately it drags with it references to the workbook it’s copied from.

:help:

godfather

21-02-2005, 11:19 AM

If the formula has cell specific references in it (absolute references) then it will take them with it.

If not, only the formula should transfer.

Can you post a typical formula that is not transferring?

That’s what I thought too GF. However, The first formula becomes the second formula even though it’s a Special Pasted, Formula Only!

Unfortunately, this is quite a small one and there are others much larger, which I don’t feel like typing individually. It’s the workbook name [Meeting-TeRapa-5-2-05.xls] that’s causing my distress.

=IF(IMPORT!AD6="","",IF(IMPORT!AD6="X","X",IF(IMPORT!AD6=0,10,IMPORT!AD6))

=IF('[Meeting-TeRapa-5-2-05.xls]IMPORT'!AD6="","",IF('[Meeting-TeRapa-5-2-05.xls]IMPORT'!AD6="X","X",IF('[Meeting-TeRapa-5-2-05.xls]IMPORT'!AD6=0,10,'[Meeting-TeRapa-5-2-05.xls]IMPORT'!AD6)))

Use paste special, formula from the right click menu.

godfather

21-02-2005, 12:11 PM

But the formula is wholly dependent on the spreadsheet that is linked in the IMPORT! function.

So it will embed the whole name, it would appear that it has no choice.

What did you expect it to transfer I wonder, as there is a hard link established?

Steven

21-02-2005, 12:14 PM

Just select the formula from inside the cell and copy it that way.

Just to clarify things GF.

“Import” is not being used as a function. It is the name of a “worksheet” within a “workbook”. Badly named possibly, but it is really just Sheet1 where one can paste values for the other sheets to do the calculating on.

I’ve got a gut feeling I might have to hold down Shift / Ctrl / Alt or something. Can’t find anything in Help either.

Don’t seem to be able to Copy and Paste from within a cell either.

Steven

21-02-2005, 12:37 PM

To select inside a cell and copy first click the cell then select the information inside the cell by selected it using the formula bar and then select copy.

Sorry Steven, I've tried that but it doesn't work.

I still get referenced to the workbook I copied from.

Steven

21-02-2005, 01:11 PM

Perhaps you could paste a very small sample of what is causing the problem including some sample data. I am unable to replicate your problem using the method I have described.

I wonder if I have a problem with the programme? I’ve just run Detect & Repair but to no avail.

Ok, can we try this?

Open a new workbook and name it Test1.

In A1 type: =IF(Sheet2!A1="","",Sheet2!A1)

Now, open a new workbook and name it Test2

Then try to copy the formula above from A1 in the Test1 Workbook to

A1 in the Test2 Workbook.

I get the following =IF([Test1.xls]Sheet2!A1="","",[Test1.xls]Sheet2!A1)

Even though I’ve used paste / special / formula, it has dragged across the reference to the Test1 Workbook.

What I want is just: =IF(Sheet2!A1="","",Sheet2!A1) without the reference to the Test1 Workbook.

Hope that makes things a little clearer.

godfather

21-02-2005, 02:57 PM

I get the same result as you, and that is the result I expect.

"Sheet2!A1!" is actually a hard reference to the "sheet1" in that source workbook, it is not a generic reference to any local "sheet1".

The problem that it would cause if it actually was a generic and portable reference are (in my case anyway) not worth thinking about!

There are ways around what you want to do, involving displaying Formulas in the cells, and Find and Replace.

Or, try breaking the links under edit-links.

I take your point GF but on the other hand, let’s say we have 1000 clients each with their own Workbook. Each Workbook you want to keep separate and not in any way dependant on the other. When making a new workbook all you want to do is “steal” the formula from one and paste it in the other to save the hassle of re-typing it. I thought it was quite simple, but for the moment that simplicity eludes me. :)

Steven

21-02-2005, 04:15 PM

Im sorry but using the method I described it works perfectly.

First do you have the formula Bar showing the option for it is in the view menu.

Second select the cell you want the formula to be copied from.

*Third highlight the text in the formula bar and select copy then press escape.

Lastly use the normal paste function and paste it into the new workbook.

*Copying the from the formula bar is just like copying from the address bar in ie or firefox.

Ahhhhhhh Steven, there is no need to be sorry.

The “Escape” key makeith the difference.

Just when I’d used a little inja…. enja….. cunning and got round the problem by removing the = sign, copying and pasting and then replacing the = sign.

Never mind, I’ll try to remember, not Ctrl, not Alt, not Shift, "Esc!"

Thanks for your trouble everyone.

Parry

21-02-2005, 07:11 PM

Hi BM, it sounds as though youve solved your problem but heres a tip for you anyway for these situations.

When you copy a formula to another book and that formula contains a sheet name then Excel assumes you want to link to the source book. When you close the book you copied from you will notice the formula also includes the full path as well.

To change the formulas all at once to refer to a sheet with the same name in the destination book (the one you copied to) do the following:-

1) Copy and paste cells into your destination book. Dont bother with paste special.

2) In the destination book select Edit|Links from the menu. A dialog will appear that lists all the links associated with the book.

3) Select the Change Source button and you will be prompted to locate the file where the source data comes from. Select the current destination book you have open and then click OK. You will see all the links to the other book disappear and the reference point to the sheet in the book your in.

regards

Graham.

Abdul~Alhazred

21-02-2005, 09:58 PM

Not quite sure what the problem is.

But if I want to copy/paste formulas from one workbook to another without irrelevant references I just put an apostrophe, ' , in front of the = then copy then paste then remove the apostrophe.

This just converts the formula to text, then reconverts it to formula when copied pasted.

Your removing the = then putting it back in does the same thing.

cheers, ab

Thanks Graham, nice to know all the alternative ways to do these things. :)

Cheers

Bob

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.