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!

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!

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!

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

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

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

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