How can I paste WITHOUT cell references changing (and without usingabsolute cell addresses) [EXCEL20

[ship]

(using Excel2003 SP3/WinXP)


Hi

How do I copy & paste WITHOUT the cell references changing, and
without using absolute cell addressess?

I KEEP needing to do this. At present my only solution is to paste a
formula into a
plain text editor and copy from there. But SURELY there is an easier
way !(?)

With thanks


Ship
Shiperton Henethe

 

[Sandy Mann]

Copy the formula from the formnula bar - without highlighting the equals
sign - then enter = in the destination cell and paste.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Pete_UK]

You could put an apostrophe in front of the = sign of a formula and
then copy that to the destinations cell(s) and then remove the
apostrophe. If you have a block of formulae to copy then you could
highlight them and do Find & Replace (CTRL-H);

Find what: =
Replace with: zz=
click Replace All.

Then copy to the destination cells and do the reverse, i.e.:

CTRL-H
Find what: zz=
Replace with: =
click Replace All.

Hope this helps.

Pete

 

[Dave Peterson]

You wrote apostrophe in your text, but used zz.

I wouldn't use the apostrophe ('). It causes nothing but heartache when trying
to clean.

 

[Pete_UK]

Dave,

the first sentence of my reply assumed just one cell to be copied (though
maybe into a range), so using an apostrophe is easy in that case. The second
sentence referred to copying a block of formulae, in which case it is a bit
tedious to edit each of them and put the apostrophe in, and then take them
out afterwards, so I suggested the Find/Replace option.

Made sense to me when I wrote it, anyway !! <bg>

Pete

 

[Dave Peterson]

I'd still stay away from that prefixcharacter. Starting with a Z (in the
formula bar) is just as simple and won't cause problems.

 

[Mike Tordoff]

Since cell references don't change during cutting and pasting...

1. Copy the formula you want to duplicate and paste it into an adjacent cell
2. Cut the formula you want to duplicate and paste it where you want it
3. Copy the formula you pasted into the adjacent cell in Step 1 and paste it
back into the original cell

 

[ship]

Yes I think Sandy wins this one.
His suggestion is still slightly fiddly, and he missed out the fact
that you need to hit Esc after
copying, but it works okay for a single cell.

For multiple cells
I couldnt get the replace tool to even find '= so, so there is thus
no way to replace
it with =
....so replacing "=" with "zz=", and then the reverse seems best for
formulae.


But what a pain in the ring all round.
I do find it absolutely astonnishing that Microsoft havent build such
a facility in (!) :-[

Cheers


Ship

 

[Sandy Mann]

Yes you are quite correct you have to press Escape or if you have a red X to
the left of the formula bar you can click that.

As far as the Replace method is concerned Don't preceed the = with an
apostrophe or enclose it in quotes and it should work OK.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk



Yes I think Sandy wins this one.
His suggestion is still slightly fiddly, and he missed out the fact
that you need to hit Esc after
copying, but it works okay for a single cell.

For multiple cells
I couldnt get the replace tool to even find '= so, so there is thus
no way to replace
it with =
....so replacing "=" with "zz=", and then the reverse seems best for
formulae.


But what a pain in the ring all round.
I do find it absolutely astonnishing that Microsoft havent build such
a facility in (!) :-[

Cheers


Ship