Forum Home
Press F1
Thread ID: 54735	2005-02-20 22:51:00	"XL" Query	B.M. (505)	Press F1

Post ID	Timestamp	Content	User
326933	2005-02-21 02:34:00	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.	B.M. (505)
326934	2005-02-21 02:57:00	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.	godfather (25)
326935	2005-02-21 03:25:00	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. :)	B.M. (505)
326936	2005-02-21 04:15:00	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.	Steven (7085)
326937	2005-02-21 05:09:00	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.	B.M. (505)
326938	2005-02-21 07:11:00	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.	Parry (5696)
326939	2005-02-21 09:58:00	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	Abdul~Alhazred (6966)
326940	2005-02-21 20:18:00	Thanks Graham, nice to know all the alternative ways to do these things. :) Cheers Bob	B.M. (505)
1 2