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
494 Views

Similar Articles

[PageSpeed] 23

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 (12004)
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:

How to apply a formula accross all the rows when the total rows are too much in no. ?
Hi, guyz i know you can drag the formula accross the rows in that column t have that formula in effect in every cell. but i have some data lik 5000 rows then how to get that formula accross each cell, its painfu holding the mouse and dragging the formula accross the rows. please advise thanks in advance sorab -- Message posted from http://www.ExcelForum.com Hi after inserting this formula in the first row double click on the lower right corner of your cell selection -- Regards Frank Kabel Frankfurt, Germany > Hi, > guyz i know you can drag the formula accross the rows in that col...

Workbook Window will not maximize
Help! I am using Excel 2007 SP2 on Windows XP. When I open my Workbook, the program window opens maximized, but the workbook window opens smaller and the workbook window does not have the maximize, minimize or close buttons like usual. I can't figure out how to fix this. Has anyone else dealt with this before? On Nov 18, 9:06=A0am, magmike <magmi...@yahoo.com> wrote: > Help! I am using Excel 2007 SP2 on Windows XP. > > When I open my Workbook, the program window opens maximized, but the > workbook window opens smaller and the workbook window does not have > the maxim...

looking up cell address
I want to create a formulae that dynamicaly alters the row references of a SUM function nested within a INDIRECT function based on a start and end date variables. In order to to this I need a lookup that returns the row number and/or cell adress of the first cell that matches or exceeds the date variable and utilises the result in a SUM function nested within the the INDIRECT formulae (If possible I want to avoid inserting an additional column in the original data table that calculates the row refrence to be used with a simple vlookup) Any help offered would be appreciated ...

downloading one year back mails in mailbox
one user having forwarding e-mail id i.e. abc@yahoo.co.in,now days that user not using yahoo id so that yahooid quota is full.so we remove that forwarding e-mail id of that users as per discussion of the mangement. now problem is last december month mails those user forwared mail to that user that mail downloading now days in mailboxes as a system delivery mail. we tried all option ..... we are waiting for reply urgent.. =?Utf-8?B?UE9QMyBDb25maWd1cmF0aW9uIGluIEZyb250IGVuZC9iYWNrIGVuZA==?= <POP3ConfigurationinFrontendbackend@discussions.microsoft.com> wrote in news:F234799F-DC4A-4...

How do I copy a screen in Excel for use in Powerpoint?
I need to copy a few Excel screens for training purposes and want to paste them into a PowerPoint slide show. Can anyone help? Alt+PrintScreen and the paste into Powerpoiint. "Bess" wrote: > I need to copy a few Excel screens for training purposes and want to paste > them into a PowerPoint slide show. Can anyone help? It shows that i have transferred it to the clipboard but it will not show me the screen in PowerPoint. Any ideas on what I'm doing wrong? "David Hepner" wrote: > Alt+PrintScreen and the paste into Powerpoiint. > > "Bess" ...

Easy way to reference a specific cell in a new worksheet
This project is for week over week sales. A new worksheet is added every week. The current week's sales are in Column E for every salesperson and the previous week's sales are in Column F. I would like the Column F to automatically reference Column E on the previous worksheet every time a new worksheet is added by copying the previous worksheet. It's not that hard to work around it now, I would just like to save time and automate the process. Is this possible? Let me know if what I am attempting to do does not make sense. TIA Hi without VBA this is not possible -- ...

Multiple date validation in a single cell
I am trying to do a rather complicated validation (I think it i anyway!) I want to allow ONLY dates of a particular format to be entered int the one cell - i.e. YYYY OR mm/YYYY OR dd/mm/YYYY For example, the same cell can have either one of these format entered, but MUST match one of them to be accepted. 1970 is OK and is captured in the cell as 1970 (not 1/01/1970) 70 is assumed to mean 1970 on entry 05/1970 is OK, captured in the cell as 05/1970 (not 1/05/1970) 5/70 or May-70 is assumed to mean 05/0970 on entry 05/06/2004 is OK 5/6/04 or 5 June 04 is assumed to mean 05/06/2004 on entr...

Publisher 03 -how to move parts of brochure to a another brochur.
how do I merge panels of info from one pub. brochure to another fcircle wrote: > how do I merge panels of info from one pub. brochure to another Copy paste from one Publication to another. Line the two publications side by side vertically on the page. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com Windows Marketplace Moderator This posting is provided "AS IS" with no warranties, and confers no rights. ...

Another question regarding Dates..
First, thanks to the replies to my previous post. How can I work out the day & month when I only know the Year and Day Number? Ie Year = 2004 day number = 302 The answer I'm looking for is 28/10/2004 Again, taking into consideration the leap years Thanks again in advance =DATE(2004,1,302) Regards, Peo Sjoblom "Anthony Slater" wrote: > First, thanks to the replies to my previous post. > > How can I work out the day & month when I only know the Year and Day Number? > > Ie > > Year = 2004 > day number = 302 > > The answer I'...

Only one table appears realtionwship wizard
I have two tables in the same folder the realtionship wizard onlys shows one table Access 2007 You are mixing terms. Tables are not in folders. Folders are subdivisions of computer drives. Databases reside in folders on a drive. Tables are in databases, in folders, on a drive. Think of fields (Database term - Access) as being the columns (Spreadsheet term - Excel) of a table. Are your two tables in the same database or in different databases but in the same file folder on the drive? -- Build a little, test a little. "Terry" wrote: > I have two tables...

formula #59
i am trying to do this:: =IF(J48<25,"",K48) just insert the info from I48 into K48 BUT =IF(J48>25) then add J48 info to I48 and place the total in K48. is this possible and how is it done =if(j48<25,"",if(j48>25,j48+i48)) -- y_not ------------------------------------------------------------------------ y_not's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19947 View this thread: http://www.excelforum.com/showthread.php?threadid=394739 A formula can only enter values into the cell in which it resides, it cannot enter data int...

copying charts into new sheets, data is pulled from old sheet
i make a sheet with a load of data and some graphs, then i want to copy the whole thing into a new sheet to process some similar but different data, all the cell references translate cleanly, and refer to the new sheet however, the graphs reflec tthe previous sheets ranges and data. how can i copy the graphs so they refer to cells on the new sheet? btw i know excel pretty well, and i could go through and drag new data on, but i would like about 30 graphs per sheet, so its tedious. thanks instead of copying just the data, use the Move or Copy Sheet option under Edit and copy the whol...

Easiest way to insert multiple copied rows?
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Currently, I copy a row, right click and choose insert copied cells. <br> This works great but only works for copying one row below. <br><br>What if I want to copy 2 or 3 rows below? <br><br>I can find blank rows, copy them, and insert them and then do this, but that is real tedious. <br><br>I wish there was a was to simply copy a row and hit some keystroke which would copy that row right below as many time as you click the keystroke. Does this exist? thanks! Try clic...

Validations from another book
In data/validation the source is =() but to access the source via another book is it simply a matter of giving the full path name? Hi see: http://www.contextures.com/xlDataVal05.html -- Regards Frank Kabel Frankfurt, Germany "Pat" <glass_patrick@hotmail.com> schrieb im Newsbeitrag news:co2ghh$6oh$1@newsg3.svr.pol.co.uk... > In data/validation the source is =() but to access the source via another > book is it simply a matter of giving the full path name? > > > See: http://www.contextures.com/xlDataVal05.html HTH Jason Atlanta, GA >-----Original Mess...

Check for data in cell
I want to check to be sure the cell selected in column C has data in it otherwise display an error message. Hi oldjay You can use the worksheet event shown below: Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' Be sure to look at a single cell If Target.Cells.Count = 1 Then ' Check if a cell iu column C is selected If Target.Column = 3 Then ' Check id the selected cell is empty If IsEmpty(Target) Then MsgBox "On empty cell" End If End If End If End S...

how may I copy color of a other cell
Hello, I need a function that copy also the full format/color/size of a cell Example A1 = USD 12'500.00 (monetary size, 12 fat, blue bottom) A2 = mon 12.03.2004 (dates, 10 not fat, white bottom) If in A3 I type: =" A1 " I would like a blue bottom, 12 fat, etc. and If I type =" A2 " a white bottom, 10 not fat, etc.... Is it possible? Thank you for any help. Thierry, Maybe you can use the Format Painter. Select the cell that has the formatting you wish to copy, and click the Format Painter. It's a paintbrush on a button on the Standard toolbar. Now click the c...

How to copy Rules & Alerts from one PC to another
I occasionally copy my Outlook.pst file from my desktop to my laptop when I travel, and then back again when I get home. I also export and import the Rules & Alerts. The problem is that the Rules & Alerts for my emails don't copy over very well. The rules are there, but they say "on other machine", and most (but not all) of the rules that reference a specific email account (e.g. when received through specified account) are missing the account specification. Since I have quite a few rules, it is laborious to fix all these rules every time I transfer my fi...

Adding Comments From Cells To Chart Points.
Hi all I have spent a large portion of my weekend trying to find the answer to this. Without much luck! I have finally come up with my own code and was hoping someone could have a look at it. I'm looking to clean it up and make it a little more efficient. Thanks in advance, Greg. Sub AddCommentsToChartPoints() Dim ws As Worksheet Dim ct As ChartObject Dim serSeries As SeriesCollection Dim ser As Series Dim Counter As Integer Dim ChartName As String Dim xVals As String Dim xAddress As String 'Loop through each worksheet in workbook For Each ws In Worksheets 'Lo...

orden en los operadores de las formulas
deseo saber cual es el orden correcto para generar formulas en una hoja de excel -- Excel "angies" <angiemarbella27@hotmail.com> wrote in message news:E03784BA-F3E8-4E54-84EA-00D151DD939A@microsoft.com... > deseo saber cual es el orden correcto para generar formulas en una hoja de > excel > -- > Excel La multiplicación y la división antes de la adición y de la substracción. Por ejemplo, si usted escribe =5+2*3, el resultado será 11. Si quiere 21 por el resultado , tiene que usar parentesis: =(5+2)*3 ...

Formulas not calculating #2
Hello, i avea quick question. i have formulas in cells. such as this in cell C1: =IF(A1="","",A1&"/ "&B1) but when i put a value in A1 it does not evaluate. i have to go into C1 and hit enter. then it evaluates. is there a setting in excel that i need to set? thanks, Hi goto 'Tools - Options - Calculate' and ebale 'Automatic Calculation' -- Regards Frank Kabel Frankfurt, Germany greg wrote: > Hello, > i avea quick question. > i have formulas in cells. such as this in cell C1: > =IF(A1="","",A1&...

multiple indexes in one document
I have a document that lists information in a table. This information has to be split into two indexes in separate parts of the same document. Trick is that one index will need to contain all of the information and the other just certain pieces. I created the main index and it is fine, however I do not know how to double mark entries for use in the second index. There are two separate mechanisms for this, and you can use one or the other. (I would discourage trying to use both at the same time.) If all the items for the second index are together in one part of the table...

Summing Every Nth Cell
I found a solution on the web for summing every Nth cell, but it is kind of convoluted and I ran into problems. Let's say I want to sum cells C4,C25,C46...etc all the way down. Basically every 21st cell. The problem might be that some of those rows are blank and others contain text. Is there an easy way to do this? Thanks One way: =SUMPRODUCT(--(MOD(ROW(C1:C999),21)=4),C1:C999) Personally, I think I'd use a helper column and put an indicator on every row I want summed. Say I put X in column D, then I could use: =sumif(d:d,"x",c:c) Then if I ever insert/delete rows,...

how do i add in numbers automatically based on adjacent cells cont
I'm trying to automatically enter data into multiple cells based on one cells data. For instance if a cell in column A = 1 then the adjacent cells in columns B, C and D will all have specific values which will be true for a value of 1 in column A. If the value in the cell in column A = 2 then the adjacent cells in columns B, C and D will have another set of specific values which will be true for a value of 2 in column A and so on. I think lookup tables is possibly the answer but the help isn't much good and I am struggling! In an out--the-way location, create a datalist tha...

How do I link several forms to one id in one table
I use the data base to record details of students I see. I currently have 1 table with one main form and several other forms that are accessed from command buttons on the main form. The forms accessed by the command buttons have a series of option buttons which help me record issues the child has. How do make the information recorded on each of the forms relate to the child and information on the main form. Hope that is clear. Thanks Hi Maxine, It seems the best way to solve your problem as asked is to make the "several other forms" subforms on the main form and let Acc...

Copy and Pasting Cells with VBA..help
I have a directory of business address to make into a database. The current format is like this: A B C R1 Co.Name R2 Add1 R3 CSZ R4 Phone R5 Blank R6 Blank R7 Co.Name (2) R8 Add1 (2) R9 CSZ(2) R10 Phone(2) R11 Blank R12 Blank and this repeats down through the spreadsheet. I need to keep the Co.Names in column A, copy/paste Add1 in B, CSZ in C and so on. Then all the data and blank cells in A must be deleted, except the company name of course. I was thinking of trying to loop this. Is this the best method? What w...