Home |
Search |
Today's Posts |
#1
|
|||
|
|||
I'm having a bizzare problem with C&P!!!!
Actually it's with copy and paste! If I copy a series of cells that I have
formulas in to use in another place when I paste the simple formulas are OK but the more complex ones get trashed. It happens if I copy and paste as you normally would and if I use special just "formulas". I've tried "all" and the same thing happens. What am I doing wrong. What should have taken a few hours has taken me all day to do cell by cell. Marc P.S. this is the reason for the rant! |
#2
|
|||
|
|||
Hi Marc,
Please post a short example of the address range you copied from, and the formulas by address, Next the address range you are pasting to and the formulas you got -- and the formulas you expected. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Marc" wrote in message link.net... Actually it's with copy and paste! If I copy a series of cells that I have formulas in to use in another place when I paste the simple formulas are OK but the more complex ones get trashed. It happens if I copy and paste as you normally would and if I use special just "formulas". I've tried "all" and the same thing happens. What am I doing wrong. What should have taken a few hours has taken me all day to do cell by cell. Marc P.S. this is the reason for the rant! |
#3
|
|||
|
|||
I've tried it several ways. I'm creating a work book to generate quotes. I
enter everything on the first page and it fills the good, better, best sheets which than fill three printable quotes on the same sheet. So once I got the sheets set I figured I could just C&P the formulas and use find/replace to make the changes. I've tried clicking the upper left square so it high lights the entire sheet and than C&P to the other two sheets using "paste special" - formulas and all - but got the same results as if I just pasted regular. I've tried copying "B11:T11" and pasting to B34:T34, "B56:T56" and "B78:T78" on one sheet and copying "AP15:25" pasting to "AP59:69", "AP103:113 on another and copying "AB4:AR44" and pasting to "AB48:AR88" and :AB92:AR132. The simple formulas either linking to another cell or adding or multpling by 2 cells are OK but anything more complex gets trashed and I used "paste special" formulas which I thought would make a "carbon copy". Marc David McRitchie" wrote in message ... Hi Marc, Please post a short example of the address range you copied from, and the formulas by address, Next the address range you are pasting to and the formulas you got -- and the formulas you expected. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Marc" wrote in message link.net... Actually it's with copy and paste! If I copy a series of cells that I have formulas in to use in another place when I paste the simple formulas are OK but the more complex ones get trashed. It happens if I copy and paste as you normally would and if I use special just "formulas". I've tried "all" and the same thing happens. What am I doing wrong. What should have taken a few hours has taken me all day to do cell by cell. Marc P.S. this is the reason for the rant! |
#4
|
|||
|
|||
Hi Marc,
Repeating: Please post a short example of the address range you copied from, and the formulas by address. It is impossible to guess what your problem is without a description of the problem: what you did, what formula you copied, from where to where, what formula go got, and what you expected. You have to show how the formula changed and what you expected. Even if we tried to unravel your long paragraph, there are no formulas -- you must provide a reproducible example of your problem. Example: ----------- A1: 2 B1: 4 C1=8 D1=ROW() E1: =SUM(A1:D1) displays 15 copy A1:E1 paste into A2, resulted in A2: 2 B2: 4 C2=8 D2=ROW() E1: =SUM(A2:D2) displays 16 What I expected was: (describe what you wanted/expected) ------------------- Are you familiar with relative and absolute addresses. Relative And Absolute Addressing .... formula containing a relative address, Excel will adjust the row and/or column references ... This type of cell reference is called absolute addressing. ... http://www.cpearson.com/excel/relative.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Marc" wrote.. I've tried it several ways. I'm creating a work book to generate quotes. I enter everything on the first page and it fills the good, better, best |
#5
|
|||
|
|||
David solved the problem and it was in the original and absolute values.
The cells that didn't paste correctly didn't have the $ making them absolute values. How do I make sure that they have the $ when I C&P? Marc "David McRitchie" wrote in message ... Hi Marc, Repeating: Please post a short example of the address range you copied from, and the formulas by address. It is impossible to guess what your problem is without a description of the problem: what you did, what formula you copied, from where to where, what formula go got, and what you expected. You have to show how the formula changed and what you expected. Even if we tried to unravel your long paragraph, there are no formulas -- you must provide a reproducible example of your problem. Example: ----------- A1: 2 B1: 4 C1=8 D1=ROW() E1: =SUM(A1:D1) displays 15 copy A1:E1 paste into A2, resulted in A2: 2 B2: 4 C2=8 D2=ROW() E1: =SUM(A2:D2) displays 16 What I expected was: (describe what you wanted/expected) ------------------- Are you familiar with relative and absolute addresses. Relative And Absolute Addressing ... formula containing a relative address, Excel will adjust the row and/or column references ... This type of cell reference is called absolute addressing. .... http://www.cpearson.com/excel/relative.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Marc" wrote.. I've tried it several ways. I'm creating a work book to generate quotes. I enter everything on the first page and it fills the good, better, best |
#6
|
|||
|
|||
THANKS
Marc "Marc" wrote in message link.net... David solved the problem and it was in the original and absolute values. The cells that didn't paste correctly didn't have the $ making them absolute values. How do I make sure that they have the $ when I C&P? Marc "David McRitchie" wrote in message ... Hi Marc, Repeating: Please post a short example of the address range you copied from, and the formulas by address. It is impossible to guess what your problem is without a description of the problem: what you did, what formula you copied, from where to where, what formula go got, and what you expected. You have to show how the formula changed and what you expected. Even if we tried to unravel your long paragraph, there are no formulas -- you must provide a reproducible example of your problem. Example: ----------- A1: 2 B1: 4 C1=8 D1=ROW() E1: =SUM(A1:D1) displays 15 copy A1:E1 paste into A2, resulted in A2: 2 B2: 4 C2=8 D2=ROW() E1: =SUM(A2:D2) displays 16 What I expected was: (describe what you wanted/expected) ------------------- Are you familiar with relative and absolute addresses. Relative And Absolute Addressing ... formula containing a relative address, Excel will adjust the row and/or column references ... This type of cell reference is called absolute addressing. ... http://www.cpearson.com/excel/relative.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Marc" wrote.. I've tried it several ways. I'm creating a work book to generate quotes. I enter everything on the first page and it fills the good, better, best |
#7
|
|||
|
|||
Marc
To change relative to absolute manually. 1. Type in the $ signs 2. F2 and highlight the address(es) in the cell and hit F4 to cycle through choices. OR you can run a macro to change all at once. Here are 4 such macros...... Sub Absolute() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlAbsolute) End If Next End Sub Sub AbsoluteRow() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlAbsRowRelColumn) End If Next End Sub Sub AbsoluteCol() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlRelRowAbsColumn) End If Next End Sub Sub Relative() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlRelative) End If Next End Sub If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + R to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the above code in there. Save the workbook and hit ALT + Q to return to your workbook. Run the macro by going to ToolMacroMacros. Gord Dibben Excel MVP On Sun, 24 Apr 2005 16:44:28 GMT, "Marc" wrote: David solved the problem and it was in the original and absolute values. The cells that didn't paste correctly didn't have the $ making them absolute values. How do I make sure that they have the $ when I C&P? Marc "David McRitchie" wrote in message ... Hi Marc, Repeating: Please post a short example of the address range you copied from, and the formulas by address. It is impossible to guess what your problem is without a description of the problem: what you did, what formula you copied, from where to where, what formula go got, and what you expected. You have to show how the formula changed and what you expected. Even if we tried to unravel your long paragraph, there are no formulas -- you must provide a reproducible example of your problem. Example: ----------- A1: 2 B1: 4 C1=8 D1=ROW() E1: =SUM(A1:D1) displays 15 copy A1:E1 paste into A2, resulted in A2: 2 B2: 4 C2=8 D2=ROW() E1: =SUM(A2:D2) displays 16 What I expected was: (describe what you wanted/expected) ------------------- Are you familiar with relative and absolute addresses. Relative And Absolute Addressing ... formula containing a relative address, Excel will adjust the row and/or column references ... This type of cell reference is called absolute addressing. ... http://www.cpearson.com/excel/relative.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Marc" wrote.. I've tried it several ways. I'm creating a work book to generate quotes. I enter everything on the first page and it fills the good, better, best |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Display Problem | Excel Discussion (Misc queries) | |||
autofilter problem | Excel Worksheet Functions | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) | |||
Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) | |||
Paper Tray selection Problem, | Excel Discussion (Misc queries) |