Paste formulas and keep them the same

[Josh Craig]

I want to copy a group of formulas from one part of a sheet and paste them in
another part of the sheet without the relative cell references changing on me.

For example if in A1 i have '=b2' I want to copy A1 and paste it in M1 and
it say '=b2' not '=n2'.

For individual cells I've been copying the text from the formula bar but I
want a quicker way to do it for multiple cells at once.

How would I go about doing this?

Thanks,

Josh

 

[Dave Peterson]

For a single (or a couple of cells), I copy|paste into the formula bar -- like
you wrote.

But for range, I'll do this:

Select the range to copy
Edit|replace
what: =
with: $$$$$=
replace all

Now all those formulas are just plain old text.

Then I do the copy|paste and go back to each range (original and pasted) and
change those $$$$$= back to =.

 

[Sheeloo]

Select and copy the range to COPY FROM
Go to the first cell in the detination range
Right-Click, choose PASTE SPECIAL and click on Paste Link

 

[Josh Craig]

Smart workaround. Thanks Dave!

For a single (or a couple of cells), I copy|paste into the formula bar -- like
you wrote.

But for range, I'll do this:

Select the range to copy
Edit|replace
what: =
with: $$$$$=
replace all

Now all those formulas are just plain old text.

Then I do the copy|paste and go back to each range (original and pasted) and
change those $$$$$= back to =.

 

[Sheeloo]

Josh/Dave,

Pl try my solution and let me know whether it works...

Select and copy the range to COPY FROM
Go to the first cell in the detination range
Right-Click, choose PASTE SPECIAL and click on Paste Link

 

[Dave Peterson]

That wouldn't copy the existing formulas. That just creates new formulas that
link back to the copied cells.

 

[Sheeloo]

Yes, you are right.... I don't know what I was thinking...

I entered =A1 in B1 and coped it down... then copied and pasted it at
another location... saw that I got =B1... read that as =A1... and posted :-(

 

[Shane Devenshire]

Hi,

Suppose you want to copy the range A1:A10 to R1:R10:

1. Select A1:A10 and choose Copy
2. Move to B1 and choose Paste
3. With B1:B10 selected choose Cut
4. Move to S1 and choose Paste
5. With S1:S10 selected choose Copy
6. Move the R1 and choose Paste.