How do I copy a cell + it's formula from one workbook to another?

For reasons i won't go into, i have to transfer hundreds of cells from one 
workbook to another. each one of these cells contains a formula

ex: 
"=SUM(IF(Production!$I$2:$I$573="HM37",IF(Production!$H$2:$H$573="AP",IF(Production!$S$2:$S$573="36H",IF(Production!$D$2:$D$573="X",Production!$Q$2:$Q$573,0))),0))-SUM(IF(Production!$I$2:$I$573="HM37",IF(Production!$H$2:$H$573="AP",IF(Production!$S$2:$S$573="36H",IF(Production!$E$2:$E$573="X",Production!$Q$2:$Q$573,0))),0))"

the new workbook is an identical copy of the old one, so the references to 
sheet "Production!" will still apply. 
i don't understand why when i select a cell from the old workbook, and copy 
- paste it into a cell in the new workbook, why it doesn't transfer the 
formula attached to it. instead it is only transferring the numerical value 
in the cell. 
this goes for all types of formulas i have tried to copy-paste so far. i 
have tried several of the paste special options, but none have the desired 
outcome.

if you could supply any help on this issue, i would greatly appreciate it.
thank you
0
please5927 (11)
10/11/2005 4:02:02 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
520 Views

Similar Articles

[PageSpeed] 39

If the new book is to be is identical copy of the old, why not just open the 
old one and save it under another name?

-- 
Ian
--
"excel _ help for the hopeless, please" <excel _ help for the hopeless, 
please@discussions.microsoft.com> wrote in message 
news:07156A7F-0393-4FF2-A606-3C75B9A1788E@microsoft.com...
> For reasons i won't go into, i have to transfer hundreds of cells from one
> workbook to another. each one of these cells contains a formula
>
> ex:
> "=SUM(IF(Production!$I$2:$I$573="HM37",IF(Production!$H$2:$H$573="AP",IF(Production!$S$2:$S$573="36H",IF(Production!$D$2:$D$573="X",Production!$Q$2:$Q$573,0))),0))-SUM(IF(Production!$I$2:$I$573="HM37",IF(Production!$H$2:$H$573="AP",IF(Production!$S$2:$S$573="36H",IF(Production!$E$2:$E$573="X",Production!$Q$2:$Q$573,0))),0))"
>
> the new workbook is an identical copy of the old one, so the references to
> sheet "Production!" will still apply.
> i don't understand why when i select a cell from the old workbook, and 
> copy
> - paste it into a cell in the new workbook, why it doesn't transfer the
> formula attached to it. instead it is only transferring the numerical 
> value
> in the cell.
> this goes for all types of formulas i have tried to copy-paste so far. i
> have tried several of the paste special options, but none have the desired
> outcome.
>
> if you could supply any help on this issue, i would greatly appreciate it.
> thank you 


0
me1 (409)
10/11/2005 4:30:56 PM
Prior to copy/paste, use find/replace to change all instances of = to '=
This will change all the formulae to text.  Then copy/paste.  Finally remove 
all the '
-- 
Gary's Student


"excel _ help for the hopeless, please" wrote:

> For reasons i won't go into, i have to transfer hundreds of cells from one 
> workbook to another. each one of these cells contains a formula
> 
> ex: 
> "=SUM(IF(Production!$I$2:$I$573="HM37",IF(Production!$H$2:$H$573="AP",IF(Production!$S$2:$S$573="36H",IF(Production!$D$2:$D$573="X",Production!$Q$2:$Q$573,0))),0))-SUM(IF(Production!$I$2:$I$573="HM37",IF(Production!$H$2:$H$573="AP",IF(Production!$S$2:$S$573="36H",IF(Production!$E$2:$E$573="X",Production!$Q$2:$Q$573,0))),0))"
> 
> the new workbook is an identical copy of the old one, so the references to 
> sheet "Production!" will still apply. 
> i don't understand why when i select a cell from the old workbook, and copy 
> - paste it into a cell in the new workbook, why it doesn't transfer the 
> formula attached to it. instead it is only transferring the numerical value 
> in the cell. 
> this goes for all types of formulas i have tried to copy-paste so far. i 
> have tried several of the paste special options, but none have the desired 
> outcome.
> 
> if you could supply any help on this issue, i would greatly appreciate it.
> thank you
0
GarysStudent (1572)
10/11/2005 4:55:02 PM
Oohhhhh.  I'd stay away from using the apostrophe for this.

I change = (equal sign) to $$$$$= (a nice unique string).

Gary''s Student wrote:
> 
> Prior to copy/paste, use find/replace to change all instances of = to '=
> This will change all the formulae to text.  Then copy/paste.  Finally remove
> all the '
> --
> Gary's Student
> 
> "excel _ help for the hopeless, please" wrote:
> 
> > For reasons i won't go into, i have to transfer hundreds of cells from one
> > workbook to another. each one of these cells contains a formula
> >
> > ex:
> > "=SUM(IF(Production!$I$2:$I$573="HM37",IF(Production!$H$2:$H$573="AP",IF(Production!$S$2:$S$573="36H",IF(Production!$D$2:$D$573="X",Production!$Q$2:$Q$573,0))),0))-SUM(IF(Production!$I$2:$I$573="HM37",IF(Production!$H$2:$H$573="AP",IF(Production!$S$2:$S$573="36H",IF(Production!$E$2:$E$573="X",Production!$Q$2:$Q$573,0))),0))"
> >
> > the new workbook is an identical copy of the old one, so the references to
> > sheet "Production!" will still apply.
> > i don't understand why when i select a cell from the old workbook, and copy
> > - paste it into a cell in the new workbook, why it doesn't transfer the
> > formula attached to it. instead it is only transferring the numerical value
> > in the cell.
> > this goes for all types of formulas i have tried to copy-paste so far. i
> > have tried several of the paste special options, but none have the desired
> > outcome.
> >
> > if you could supply any help on this issue, i would greatly appreciate it.
> > thank you

-- 

Dave Peterson
0
petersod (12005)
10/11/2005 6:45:47 PM
Hey Hopeless!!! I see you are getting some solutions to the formula copy 
problem!
Will see you in the morning!!! Mark

"excel _ help for the hopeless, please" wrote:

> For reasons i won't go into, i have to transfer hundreds of cells from one 
> workbook to another. each one of these cells contains a formula
> 
> ex: 
> "=SUM(IF(Production!$I$2:$I$573="HM37",IF(Production!$H$2:$H$573="AP",IF(Production!$S$2:$S$573="36H",IF(Production!$D$2:$D$573="X",Production!$Q$2:$Q$573,0))),0))-SUM(IF(Production!$I$2:$I$573="HM37",IF(Production!$H$2:$H$573="AP",IF(Production!$S$2:$S$573="36H",IF(Production!$E$2:$E$573="X",Production!$Q$2:$Q$573,0))),0))"
> 
> the new workbook is an identical copy of the old one, so the references to 
> sheet "Production!" will still apply. 
> i don't understand why when i select a cell from the old workbook, and copy 
> - paste it into a cell in the new workbook, why it doesn't transfer the 
> formula attached to it. instead it is only transferring the numerical value 
> in the cell. 
> this goes for all types of formulas i have tried to copy-paste so far. i 
> have tried several of the paste special options, but none have the desired 
> outcome.
> 
> if you could supply any help on this issue, i would greatly appreciate it.
> thank you
0
Maggard (1)
10/11/2005 10:48:02 PM
Reply:

Similar Artilces:

Using variable names for cells
I seem to remember a technique where I could assign a variable name to the contents of a cell so that whenever I wanted to use the contents, all I had to do was call up the variable name. Unfortunately I cannot find the way to set up the process. Any suggestsions or ideas would be appreciated. Thanks and a Happy New Year. -- Take out the trash to reply '05 FLHTCUI Hi dim rng as range set rng=activesheet.range("A1") msgbox rng.value -- Regards Frank Kabel Frankfurt, Germany Ultraglide wrote: > I seem to remember a technique where I could assign a variable name to >...

Average formula where blank cells are counted as zeros
I am trying to write an average formula that takes into account the blank cells. I had only cells A2, A4, A6 filled out, but when I do =average(A1:A6), it is not treating the blanks as zeros so the answer is much higher than it should be. Should I do a logic formula within the cells? If you know the range, you could do the division yourself: =sum(a1:a6)/6 But this would include other non-numeric cells in the count of cells (6), too. krwelling wrote: > > I am trying to write an average formula that takes into account the blank > cells. > > I ha...

Find MAX data in sheet (Cell)
How would one go about finding the cell that contains the MAX info, o say any/all cells that exceeds 8,000 characters/spaces in a Excel page -- confuzedagai ----------------------------------------------------------------------- confuzedagain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2943 View this thread: http://www.excelforum.com/showthread.php?threadid=49148 1. Highlight the entire document 2. Select Format->Conditional Formatting 3. Select "Formula Is" 4. Input this formula =LEN(A1)>8000 5. Format with something obvious like green bac...

Possible to copy and paste to a new outlook msg?
Is it possible to create a macro to copy the info from a few specific cells, create a new outlook message and paste the copied info into the new message? Thanks. Hi Derrick, Take a look at John Walkenbach's Sending Personalized Email from Excel, John Walkenbach, Tip 86, Using Outlook http://www.j-walk.com/ss/excel/tips/tip86.htm Mail Merge - Without Word, John Walkenbach, Tip 92, in Excel, of course. (This is the one with the Elephant -- Elephants R Us). http://www.j-walk.com/ss/excel/tips/tip92.htm You could also do this with a hyperlink, but may run into problems with length....

Can a macro open another worksheet?
Hello, I've got a macro that I need to run on about 200 spreadsheets. Is there a way to get it to select and open all 200 spreadsheets? Thanks, Art. Are they all in the same folder? In fact, do you need to open all the workbooks in a folder? Or all the workbooks in multiple known folders? If you have files in one folder--but you want to choose (click on the first and ctrl-click on subsequent in the file|open dialog), you can do something like: Option Explicit Sub testme() Dim myFileNames As Variant Dim iCtr As Long Dim wkbk As Workbook myFileNames = Ap...

Formula Question (MPS)
i have a formula i use in a spreadsheet... = IF(YEAR(O$1)-YEAR(E3)=105,,YEAR(O$1)-YEAR(E3)) I use it to caculate ages where the current date is in cell O1 and the Person's birthday is shown in cell E3. It doesn't add up right because if the birthday is later in the year than the current day (shown in cell O1. ) It works fine if the birthday is. If the birthday has already happened this year, then the formula works fine. How shoud i adjust it? thanks. Mike instead of adjusting, how about using =DATEDIF(E3,O1,"y") or =DATEDIF(E3,TODAY(),"y") ?? ...

Cells not sorting right with other worksheets
I have some problems!!! First I have multiple worksheets in a workbook. And in one column that has some names. and the columns next to them has data which is yes/no go/ngo for each person. If I add a person it will update itself in each worksheet. But I need to sort it. So I sort it and it sorts fine in the worksheet I'm working in. But with the other worksheets it sorts it but the data for each person doesn't stay with the person... I hope I explained this problem to where someone can understand. PLEASE HELP!!! How do you have the referencews set between each worksheet? &qu...

Leave copies on server with Exchange 2003
Hi all, I have a user who normally connects to an exchange server in the office, but he likes to connect to his mail box via pop3 on his phone/pda when out and about. The trouble is that the pda downloads his entire mailbox every time and removes them from his inbox on the server, even when 'Leave a copy on the server' is checked. I've tested this in the office using Outlook Express to confirm that this is happening. How can I have the server leave a copy of everything in the exchange mialbox, while sending new items to the device which requests them? Many thanks, Garet...

How to find cells with links to other workbooks?
I have a spreadsheet which shows under the edit menu links to other workbooks. I have tried to find where the links are without success. Is there a easy wy of finding such links? Hi you may try http://oaltd.co.uk/MVP/Default.htm and search for Findlink.zip (http://oaltd.co.uk/DLCount/DLCount.asp?file=FindLink.zip) "Bathonian" wrote: > I have a spreadsheet which shows under the edit menu links to other > workbooks. I have tried to find where the links are without success. Is there > a easy wy of finding such links? ...

Summing cells in pivot tables
I have a problem summing cells in a pivot table. When I double-click on a cell that I want to add, the formula from that cell gets added. Eventually I run out of characters. Is there a way to switch how the cell gets picked up. I can manually enter the cell addresses that I am trying to sum but that takes forever. Thanks, Ted There are instructions here for adding the Generate GetPivotData button to a toolbar, and toggling the feature on and off: http://www.contextures.com/xlPivot06.html Ted wrote: > I have a problem summing cells in a pivot table. When I double-cli...

Formula that checks balances
I have a long list of formulas in column "E". I need to know at a glance whether any value in this column is inequal to zero without having to page down all the way through thousands of rows. Summing the column is no good because there could be offsetting values (e.g. +5 + -5 = 0). I would like a formula I could place in one cell that would indicate if every formula in the column evaluates to zero or not (rounded to 2 decimals). Can anyone help? Thanks! "xp" wrote: > I would like a formula I could place in one cell > that would indicate if e...

Conditional format if cell=0 then font colour same as background .
I have linked a cell from another sheet. If the original cell is blank, 0 shows in the linked cell (text would be typed in source cell). To handle this, I conditionally formatted the linked cell, so if it equals zero, the font colour is the same as the background colour of the cell so you can't see the zero But the zero still prints even though it is not showing prior to printing Try the formula =IF(Sheet1!F14="","",Sheet1!F14) in the linked cell. HTH Sandy -- to e-mail direct replace @mailintor.com with @tiscali.co.uk "Paligap" <Paligap@discu...

Share Workbooks and Comments #3
Can anyone help me with this one... I have a simple spreadsheet which is shared and resides on a server. Apprx 6 people access and modify the sheet and save their modifications back to the server. No 2 people ever change the same cell so there are never any conflicts. The problem I have is that often the comments in a cell do not upload to the server. The user is unaware of this util he closes and re-opens the workbook to find that all of his changes have been saved but none of his comments. I have been getting the users to make their own copy at the end of the day and if they e-mail that c...

Refresh pivot chart when info in a cell changes
To all, I am looking for help with a Macro. What I ultimately want to do is refresh a pivot chart when the user makes a selection from a list (in a data validation cell). For example, I am an end user that selects my territory from a "drop down". The pivot chart will update to the information that is directly related to my territory. Thanks in advance for your help. Excel 2007, PivotTable With Table dependent drop-downs instead of data validation drop-downs. With non-event-driven macros. http://www.mediafire.com/file/ygzjqyi2j1x/03_09_10.xlsm ...

formula #28
how do i show the formula when i need to print Hi You can use Ctrl and ` (which is to the right of number 1 on your heyboard) or you can use Tools / Options / View / Formulas (if my memory serves me right!) Hope this helps. -- Andy. "martin ekins" <martin_ekins69@hotmail.com> wrote in message news:5BE10AD0-0759-4AED-99A9-02203459FD0F@microsoft.com... > how do i show the formula when i need to print ...

Display value of cell
Using Excel 2000 In a cell the formula shows instead of the value/result, how do I correct this? I want to see the formula. Reply to kdfoxca@yahoo.com Thanks. Are all formulas cells show the formulas or just one? Maybe you have a space before the = (Excel think it is text now) Or your cell is format as text -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Kenya" <anonymous@discussions.microsoft.com> wrote in message news:050b01c3942b$e28b7240$a301280a@phx.gbl... > Using Excel 2000 > In a cell the formula shows instead of the value/resul...

how do i search an excel sheet for links to another spreadhseet?
Is there a Find function that allows me to look for links in a sheet? When I can't find links, I'll use Bill Manville's FindLink program: http://www.oaltd.co.uk/MVP/Default.htm Flutie99 wrote: > > Is there a Find function that allows me to look for links in a sheet? -- Dave Peterson ...

Open & update another file (through macro) while running macro
I am using MS Excel 2003 and I need help to solve my problem. One excel file (suppose A.xls) in which I’ll update my graph and the data is available in another file (suppose B.xls). What I am doing (through macro) is that I have created one button (in A.xls) and when I pressed it; its open the file B.xls (the data file) and at their I select month from drop down from which its update the pivot table based on the selection month and then same I was doing with another data excel file (suppose C.xls). Query : Is there any solution that when I pressed button from file A.xls its ...

No more new fonts may be applied to this workbook #2
I have Excel 2003 (11.6113.5703) running on windows XP. I have loaded the latest update from the web. When I try to change the font size of text in the axis or label of a chart, a message shows "No more new fonts may be applied to this workbook." When I do a print preview of the charts, the same message shows up and then the print previews would be displayed. I'd appreciate if you can tell me the fix. Jon Peltier has some information on this in his Charting FAQ article: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=209#jon025 and at his web site (use th...

Cell Reference #3
In a workbook, is it possible to know all the cells where a particular cell is referenced in a calculation. Specifically, if I make a change in one cell, what are all the cells that will be affected. ...

Maximum number of characters in a cell
Is there a way to format a cell so that it can contain more than 255 characters? Bill Hi Bill, > Is there a way to format a cell so that it can contain more than 255 > characters? A cell can contain about 32000 characters, but Excel will display only about a 1000 of them (depending on font and fontsize). Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com If you would add some forced (manual) line breaks (<Alt> <Enter>) in your text at opportune locations, you will find that you can *display* much more text. -- HTH, RD ----------------------------------...

merge cells with a twist
A B C joe @ domain.com trying to merge cells in a row so that column a(joe) b(@) c(domain.com) and that result is joe@domain.com Thank you. =A1&B1&C1 -- Regards, Peo Sjoblom (No private emails please) "jd" <jdumont@@novuscom.net> wrote in message news:uaAWelbgFHA.3436@tk2msftngp13.phx.gbl... >A B C > joe @ domain.com > > trying to merge cells in a row so that column a(joe) b(@) c(domain.com) > and > that result is joe@domain.com > Thank you. > > =hyper...

Deleting a word from a Cell
Hi Can anyone help, i am trying to deletel the last word from a Cell My cell currently read: - M04274059519 Total and I need a formula that will delete the "total" word Would really appreciate some help linexe On Jan 21, 7:27=A0am, Linexe <l.clark...@hotmail.co.uk> wrote: > Hi > > Can anyone help, i am trying to deletel the last word from a Cell > > My cell currently read: - =A0M04274059519 Total > > and I need a formula that will delete the "total" word > > Would really appreciate some help > > linexe C...

cannot copy from one pub to another
all of a sudden, i cannot copy and paste from one ms pub publication to another ms pub publication. here's what i've done: i ran disk cleanup. i ran norton live update. i ran windows update. i ran office 2000 update. i ran detect and repair inside help in pub 2000. none of the above helped. you said "is clipboard set to automatic in computer services?" can you tell me where computer services is? i looked in control panel, administrative tools, component services and computer management. I ended up in console root, component services, computers, my computer, found ms pu...

referring cell in macro function
If a create a macro intended to be used as a function in a worksheet is there a property of the referring cell? I want to know what cell the formula is in. Dim callCell As String callCell = Application.Caller.Address Returns the cell address of the cell where the UDF is used. "zxcv" <zxcvnosend@yahoo.com> wrote in message news:aef3cafd-4c52-40a6-8348-ebf957b3abad@a21g2000yqn.googlegroups.com... > If a create a macro intended to be used as a function in a worksheet > is there a property of the referring cell? > > I want to know what cell the form...