Copying a range of data without adjusting formulas

[Captain Jack Flak]

I often have to create several versions of a report that summarizes data in
slightly different ways. It is easiest to do this by copying an existing
form to another section of the same worksheet, however I cannot stop the
formulas from "adjusting". This is not a case where I want to use absolute
cell references. Other spreadsheets have a way to copy a range of cells
without "updating" the cell references within the formulas. Is anyone aware
of a procedure or option, when copying a range of data, that will prevent
references within formulas from updating?

 

[Tyro]

What do you want? For example if the fomula in cell B! references cell A1,
what should it reference after you copy it to, say, cell M16?

Tyro

 

[The Rain]

Just add $ in from of the leter or number or both.
A1>>relative $A$1 absolute
You can toggle between then by doing this :
Select the cell with the formula.
Click on the "f" by the formula bar (function)
Press F4 back and forth.
Have a good day.

 

[Ragdyer]

The easiest way is to "unformula" your formulas.

Change them to text strings, copy, paste in the new location, then return
them to being formulas.

Replace the equal sign with something unique so that XL doesn't recognize
them as formulas.

For example:
Select the cells in question, then,
<Edit> <Replace>
In "Find What" enter the equal sign ( = ),
In "Replace With" enter
^^^
Then <Replace All>

While the cells are *still* selected,
Right click in the selection and choose "Copy",
Navigate to the new location and paste them.

Then, reverse the procedure:
<Edit> <Replace>
In "Find What" enter
^^^
In "Replace With" enter the equal sign
=
Then <Replace All>

All references remain unchanged.

 

[Tyro]

The OP states:

This is not a case where I want to use absolute cell references

Tyro

 

[Captain Jack Flak]

if the formula in C1 references A1, and I copy a range of cells including C1
to another location and C1 winds up being M1 I still want the formula to
reference A1, but for a variety of reasons I prefer not to use absolute cell
references. This used to be very easy in 123 and in another lifetime
Supercalc5
other spreadsheets.

 

[Captain Jack Flak]

Thank you... this will work.

The easiest way is to "unformula" your formulas.

Change them to text strings, copy, paste in the new location, then return
them to being formulas.

Replace the equal sign with something unique so that XL doesn't recognize
them as formulas.

For example:
Select the cells in question, then,
<Edit> <Replace>
In "Find What" enter the equal sign ( = ),
In "Replace With" enter
^^^
Then <Replace All>

While the cells are *still* selected,
Right click in the selection and choose "Copy",
Navigate to the new location and paste them.

Then, reverse the procedure:
<Edit> <Replace>
In "Find What" enter
^^^
In "Replace With" enter the equal sign
=
Then <Replace All>

All references remain unchanged.

 

[Learning Excel]

Maybe I'm too new but it does not make any sense to me why you can not use
absolute reference, I tried and it worked perfect. Can someone explain it to
me?

 

[Mike Rogers]

Learning

The OP said in an earlier post in this thread:
"but for a variety of reasons I prefer not to use absolute cell
references."
This kinda excludes the use of absolute references from the solution,
although it might be a good one.

Mike Rogers

 

[Learning Excel]

Thanks Mike but that still do not answer my question directed to the learning
process.
What's the difference in the outcome between using absolute reference and
not using it? If absolute reference works, then why going thru the trouble of
doing all the copying , finding, replacing, pasting, finding, replacing
again...

 

[Learning Excel]

Got to go. Hoping someone get the me good answer, will be appreciate it.
Thanks .

 

[Ken Johnson]

Thanks Mike but that still do not answer my question directed to the learning
process.
What's the difference in the outcome between using absolute reference and
not using it? If absolute reference works, then why going thru the trouble of
doing all the copying , finding, replacing, pasting, finding, replacing
again...

Hi Learning Excel,

Absolute references are not as absolute as you might think.

To illustrate, try this formula in A1...

=$B$1

now select B1 then either cut it then paste it into B2 or, with drag
and drop turned on, drag B1 down to B2, then notice that the formula
in A1 has changed to =$B$2.

One way of avoiding changes like the above is to use the INDIRECT
function. Repeating the above with =INDIRECT("B1") in A1 keeps the
formula always referencing B1 after B1 is Cut/Pasted or dragged and
dropped elsewhere.

Ken Johnson

Ken Johnson

 

[Learning Excel]

That's what learning is about.
Thanks a lot Ken Johnson, my question is answered.
Appreciate it.

 

[RagDyeR]

You're welcome, and thanks for the feed-back.

 

[Ken Johnson]

Hi Learning Excel

You're welcome.

Ken Johnson

 

[Bug Menot]

I've figured this one out. Sort of.

Step one: use a Mac.

Step two: Copy the cells you want. Hit escape, so that the source range is no longer surrounded by a marquee. Now go to where you want the cells to go, and do Edit > Paste Special ..., and specify VALU. Lo and behold, your formulas are moved without updating.

Basically, what happens is that you trick Excel into treating the cell contents as text rather than formulae, so it doesn't try to get clever with them. Happily, it doesn't treat them like text to the extent of not interpreting them as formulae once they're in the sheet.

This is under Excel for Mac 2004. I've tried with Excel 2003 on the PC, but it seems that as soon as you clear the marquee, the cells disappear from the clipboard. There may be a way to do it, and it may work in other versions, i have no idea.

 

[Tim Zych]

I like to:

Select all the formulas I want to move.
Edit -> Replace (Ctrl + H)
Find what: =
Replace with: XXXQQ

Copy them wherever I want.

Edit -> Replace
Find what: XXXQQ
Replace with: =

 

[Olivier Beaujean]

This is very smart ! thank you very much.

 

[Olivier Beaujean]

This is very smart ! many thanks

I often have to create several versions of a report that summarizes data in
slightly different ways. It is easiest to do this by copying an existing
form to another section of the same worksheet, however I cannot stop the
formulas from "adjusting". This is not a case where I want to use absolute
cell references. Other spreadsheets have a way to copy a range of cells
without "updating" the cell references within the formulas. Is anyone aware
of a procedure or option, when copying a range of data, that will prevent
references within formulas from updating?