pasting formulas fails and pastes contents

[sgualandri]

I've been using Excel 2004 for Mac (11.2.5) for quite some time. I
continue to have problems pasting formulas. I copy a formula and paste
it into a cell. If it try to paste it a couple of times into different
cells, it eventually fails. Excel will paste the cell values instead
of the formula. This happens if I use normal cut/paste. This also
happens if I paste special.

Anyone else having issues with the paste command? I don't know how
many times my Excel sheets have had errors because it pasted a value
instead of a formula.

Steve

 

[JE McGimpsey]

I've been using Excel 2004 for Mac (11.2.5) for quite some time. I
continue to have problems pasting formulas. I copy a formula and paste
it into a cell. If it try to paste it a couple of times into different
cells, it eventually fails. Excel will paste the cell values instead
of the formula. This happens if I use normal cut/paste. This also
happens if I paste special.

Anyone else having issues with the paste command? I don't know how
many times my Excel sheets have had errors because it pasted a value
instead of a formula.

I've never seen this happen and can't reproduce it.

I'm a bit confused by your description, however - you say you try to
paste it "a couple of times", but "it eventually fails".

Do you mean that you copy a cell, paste it into two different cells,
then copy another cell, paste it into two different cells, etc., then at
some unspecified number of iterations, instead of pasting the formula,
it pastes the value?

Or are you actually referring to a large(r) number of pastes of a single
copy into other single cells?

Can you be more specific about what you're doing? Even if the phenomenon
is not always consistent, can you describe exactly what you do to make
it happen in a particular instance?

 

[jimdilger]

Steve,
I have seen this. It occurs if you do "something else" in between
pastes. "Something else" could be typing in a cell, formatting a cell,
etc. So, complete your pasting tasks before starting on another task!
See if that does the trick.
Jim

 

[jimdilger]

Steve,
Just to continue my reply...

When you copy a cell(s) to the clipboard, you see a moving marquee
around the cell(s). This persists until the formula is no longer on the
clipboard.

Older versions of Excel would paste nothing if the formula was not on
the clipboard any more. That was better than pasting the contents of
the cell. At least you had a warning that something was amiss.

Ideally, the formula should remain on the clipboard until you replace
it with something else... That's what <nearly> every other application
in the universe does!

Jim

 

[sgualandri]

To Clarify...

I have a formula in one cell. I want to copy that formula into several
cells. The cells are no consecutive so I cannot perform the paste at 1
time. I may need to paste the formula into 3 or 4 different cells. I
assumed that the formula would remain in the clipboard until something
else overwrote it. Excel simply stops pasting the formula after the
2nd or 3rd time and it pastes the contents from the original cell
instead of the formula.

Let say that I want to copy the formula from cell A1 to several
different locations (eg. A10, C20, D30, ...). The formula pastes
correctly into cell A10. Pasting again into C20 or D30 is a crap
shoot. The formula may get pasted or it could just be the contents.

Steve

 

[sgualandri]

Thanks Jim.

I'll try this. I do not remember if I did something else in between
pastes but it is very possible. I'll watch more closely and track my
steps more carefully. I did not expect the contents of the clip board
to go away until I put something else into the clipboard.

Steve

 

[sgualandri]

I just tried Jim's suggestion. He is correct. If I do another
operation between pastes, the formula goes away.

Here is what I did.
1) created a simple formula in E1 =sum(b1:d1)
2) copied the formula from E1 into E2 and E3
3) went to some random cell (eg b24) and typed my name
4) went to E4 and did a paste operation
5) went to E5 and did a paste operation

Cells E1, E2 and E3 all have the correct formula in them. Cells E4 and
E5 do not have a formula in them at all. The contents of E1 was pasted
into E4 and E5. It looks like typing in between paste operations did
indeed remove the formula from the clipboard. In fact, the marching
marque did disappear as soon as I typed my name in step 3.

Steve

 

[CyberTaz]

If you need to do this frequently & are *not* copying more than one cell...
After copying, Select the first cell to be pasted to, Cmd+Click the
additional cells, *then* paste all at once.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Bob Greenblatt]

I've been using Excel 2004 for Mac (11.2.5) for quite some time. I
continue to have problems pasting formulas. I copy a formula and paste
it into a cell. If it try to paste it a couple of times into different
cells, it eventually fails. Excel will paste the cell values instead
of the formula. This happens if I use normal cut/paste. This also
happens if I paste special.

Anyone else having issues with the paste command? I don't know how
many times my Excel sheets have had errors because it pasted a value
instead of a formula.

Steve

Very interesting. I never noticed this before. I think it is a new bug. If
you do something in between paste actions that causes the marquee to
disappear, the clipboard should be purged, and paste no longer allowed.
Follow bob's suggestion of command clicking to select as many non-contiguous
cells as you want before pasting, and the formula will be properly pasted
into each.

 

[Patrick McMillan [MSFT]]

I wanted to follow up on this one, but first had to do some research with
the team here. What I found is that the behavior you're describing was
actually added to Excel in the 2001 version. The rational was that most apps
keep copied data on the clipboard until it's replaced. In Excel's case,
users would inadvertently exit "copy mode" (that is, do something to cause
the copy marquee to go away) and then find that they couldn't paste until
re-selecting and re-copying the data. Now, when you copy/cut a range, we put
HTML, PICT, and Text on the clipboard so that it is available after the
marquee is gone. Excel formatting like Conditional Formatting, Data
Validation, List Manager, Pivot Tables, and the formula itself are not on
the clipboard, so they don't persist in this scenario. Note that this is
Mac-only behavior. The Win Excel team never implemented it.

Hope that clarifies things.

Pat

Very interesting. I never noticed this before. I think it is a new bug. If
you do something in between paste actions that causes the marquee to
disappear, the clipboard should be purged, and paste no longer allowed.
Follow bob's suggestion of command clicking to select as many non-contiguous
cells as you want before pasting, and the formula will be properly pasted
into each.

--
Pat McMillan
Macintosh Business Unit
Microsoft Corp.

This posting is provided ³AS IS² with no warranties, and confers no rights.