How copy a range o cells without adjusting formulas?
 

Sometimes you need to to copy a range of cells with formulas without
adjusting - meaning any cells references remain unaltered. I know you can
$A$1 etc but sometimes you need to 'not adust' and then later adj etc. And
its worksome to input all the $'s and thn remove them. also I can move the
range instead of copy but the I lose the original range that I need.
Thank you.

How copy a range o cells without adjusting formulas?
 

If it's just one cell, I copy it from the formula bar and paste into the formula
bar.

If it's a bunch of cells, I do this:

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

Do the copy|paste while my "formulas" are just text.

Tnen reverse it (in both locations).
edit|Replace
what: $$$$$
with: =
replace all

And the Text formulas will change back to formula formulas.

Oliver wrote:

Sometimes you need to to copy a range of cells with formulas without
adjusting - meaning any cells references remain unaltered. I know you can
$A$1 etc but sometimes you need to 'not adust' and then later adj etc. And
its worksome to input all the $'s and thn remove them. also I can move the
range instead of copy but the I lose the original range that I need.
Thank you.

--

Dave Peterson

How copy a range o cells without adjusting formulas?
 

If you remove the = sign from the beginning of the formulas, you
"unformulate" them, and make them simple text.
When you copy text between ranges, there's really *nothing* to change ... is
there?

So, select your range of formulas, then:
<Edit <Replace,
In the "Find What" box, enter
=
In the "Replace With" box, enter
^^^
Then "Replace All".

Now, right click in the selection, choose "Copy",
Navigate to your new location,
Right click in the top left cell of the new range,
Choose "Paste"

Then again
<Edit <Replace,
And we reverse the action,
In the "Find What" box, enter
^^^
In the "Replace With" box, enter
=
Then "Replace All".

And you should have your formulas repositioned, with the exact, same cell
references.

Now go back, and either change your original formulas, if they're in the
same WB, or, it they're in a different WB, you could simply close without
saving to preserve their original configuration.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Oliver" wrote in message
...
Sometimes you need to to copy a range of cells with formulas without
adjusting - meaning any cells references remain unaltered. I know you can
$A$1 etc but sometimes you need to 'not adust' and then later adj etc. And
its worksome to input all the $'s and thn remove them. also I can move the
range instead of copy but the I lose the original range that I need.
Thank you.

How copy a range o cells without adjusting formulas?
 

Thank you very much. I think Microsoft could add an option to to "Paste
Special" without adjusting.

"Dave Peterson" wrote:

If it's just one cell, I copy it from the formula bar and paste into the formula
bar.

If it's a bunch of cells, I do this:

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

Do the copy|paste while my "formulas" are just text.

Tnen reverse it (in both locations).
edit|Replace
what: $$$$$
with: =
replace all

And the Text formulas will change back to formula formulas.

Oliver wrote:

Sometimes you need to to copy a range of cells with formulas without
adjusting - meaning any cells references remain unaltered. I know you can
$A$1 etc but sometimes you need to 'not adust' and then later adj etc. And
its worksome to input all the $'s and thn remove them. also I can move the
range instead of copy but the I lose the original range that I need.
Thank you.

--

Dave Peterson

How copy a range o cells without adjusting formulas?
 

Thank you very much. They could really add an option to Paste without
adjusting.

"Ragdyer" wrote:

If you remove the = sign from the beginning of the formulas, you
"unformulate" them, and make them simple text.
When you copy text between ranges, there's really *nothing* to change ... is
there?

So, select your range of formulas, then:
<Edit <Replace,
In the "Find What" box, enter
=
In the "Replace With" box, enter
^^^
Then "Replace All".

Now, right click in the selection, choose "Copy",
Navigate to your new location,
Right click in the top left cell of the new range,
Choose "Paste"

Then again
<Edit <Replace,
And we reverse the action,
In the "Find What" box, enter
^^^
In the "Replace With" box, enter
=
Then "Replace All".

And you should have your formulas repositioned, with the exact, same cell
references.

Now go back, and either change your original formulas, if they're in the
same WB, or, it they're in a different WB, you could simply close without
saving to preserve their original configuration.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Oliver" wrote in message
...
Sometimes you need to to copy a range of cells with formulas without
adjusting - meaning any cells references remain unaltered. I know you can
$A$1 etc but sometimes you need to 'not adust' and then later adj etc. And
its worksome to input all the $'s and thn remove them. also I can move the
range instead of copy but the I lose the original range that I need.
Thank you.