[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[_2_]]

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

"Captain Jack Flak" <Captain Jack wrote in
message ...
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?

[The Rain]

Just add $ in from of the leter or number or both.
A1relative $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.
--
Can''''t hear the Rain ?
Then listen to it.


"Captain Jack Flak" wrote:

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?

[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 procedu
<Edit <Replace
In "Find What" enter
^^^
In "Replace With" enter the equal sign
=
Then <Replace All

All references remain unchanged.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Captain Jack Flak" <Captain Jack wrote in
message ...
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[_2_]]

The OP states:

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

Tyro

"The Rain" wrote in message
...
Just add $ in from of the leter or number or both.
A1relative $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.
--
Can''''t hear the Rain ?
Then listen to it.


"Captain Jack Flak" wrote:

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?

[Captain Jack Flak[_2_]]

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.

"Tyro" wrote:

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

"Captain Jack Flak" <Captain Jack wrote in
message ...
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?

[Captain Jack Flak[_2_]]

Thank you... this will work.

"Ragdyer" wrote:

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 procedu
<Edit <Replace
In "Find What" enter
^^^
In "Replace With" enter the equal sign
=
Then <Replace All

All references remain unchanged.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Captain Jack Flak" <Captain Jack wrote in
message ...
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?

[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?
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Captain Jack Flak" wrote:

Thank you... this will work.

"Ragdyer" wrote:

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 procedu
<Edit <Replace
In "Find What" enter
^^^
In "Replace With" enter the equal sign
=
Then <Replace All

All references remain unchanged.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Captain Jack Flak" <Captain Jack wrote in
message ...
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?

[Mike Rogers[_2_]]

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" wrote:

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?
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Captain Jack Flak" wrote:

Thank you... this will work.

"Ragdyer" wrote:

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 procedu
<Edit <Replace
In "Find What" enter
^^^
In "Replace With" enter the equal sign
=
Then <Replace All

All references remain unchanged.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Captain Jack Flak" <Captain Jack wrote in
message ...
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?

[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...
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Mike Rogers" wrote:

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" wrote:

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?
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Captain Jack Flak" wrote:

Thank you... this will work.

"Ragdyer" wrote:

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 procedu
<Edit <Replace
In "Find What" enter
^^^
In "Replace With" enter the equal sign
=
Then <Replace All

All references remain unchanged.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Captain Jack Flak" <Captain Jack wrote in
message ...
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?

[Learning Excel]

Got to go. Hoping someone get the me good answer, will be appreciate it.
Thanks .
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Captain Jack Flak" wrote:

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?

[Ken Johnson]

On Jan 14, 2:41 pm, Learning Excel
wrote:
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...
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.

"Mike Rogers" wrote:
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" wrote:

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?
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.

"Captain Jack Flak" wrote:

Thank you... this will work.

"Ragdyer" wrote:

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 procedu
<Edit <Replace
In "Find What" enter
^^^
In "Replace With" enter the equal sign
=
Then <Replace All

All references remain unchanged.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Captain Jack Flak" <Captain Jack wrote in
...
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?

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.
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Learning Excel" wrote:

Got to go. Hoping someone get the me good answer, will be appreciate it.
Thanks .
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Captain Jack Flak" wrote:

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?

[RagDyeR]

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

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Captain Jack Flak" wrote in
message ...
Thank you... this will work.

"Ragdyer" wrote:

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 procedu
<Edit <Replace
In "Find What" enter
^^^
In "Replace With" enter the equal sign
=
Then <Replace All

All references remain unchanged.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Captain Jack Flak" <Captain Jack wrote in
message ...
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?

[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: =

--
Tim Zych
www.higherdata.com
Compare data in worksheets and find differences with Workbook Compare
A free, powerful, flexible Excel utility


<Bug Menot wrote in message ...
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.

[Olivier Beaujean]

This is very smart ! thank you very much.

On Sunday, January 13, 2008 7:38 PM Captain Jack Fla wrote:

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?


On Sunday, January 13, 2008 8:00 PM Tyro wrote:

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


On Sunday, January 13, 2008 8:05 PM TheRai wrote:

Just add $ in from of the leter or number or both.
A1relative $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.
--
Can''''t hear the Rain ?
Then listen to it.


"Captain Jack Flak" wrote:


On Sunday, January 13, 2008 8:08 PM Ragdyer wrote:

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 procedu
<Edit <Replace
In "Find What" enter
^^^
In "Replace With" enter the equal sign
=
Then <Replace All

All references remain unchanged.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Captain Jack Flak" <Captain Jack wrote in
message ...
in
absolute
aware


On Sunday, January 13, 2008 8:19 PM Tyro wrote:

The OP states:

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

Tyro


On Sunday, January 13, 2008 9:31 PM CaptainJackFla wrote:

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.

"Tyro" wrote:


On Sunday, January 13, 2008 9:31 PM CaptainJackFla wrote:

Thank you... this will work.

"Ragdyer" wrote:


On Sunday, January 13, 2008 10:13 PM LearningExce wrote:

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?
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Captain Jack Flak" wrote:


On Sunday, January 13, 2008 10:29 PM Mike06034 wrote:

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" wrote:


On Sunday, January 13, 2008 10:41 PM LearningExce wrote:

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...
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Mike Rogers" wrote:


On Sunday, January 13, 2008 10:51 PM LearningExce wrote:

Got to go. Hoping someone get the me good answer, will be appreciate it.
Thanks .
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Captain Jack Flak" wrote:


On Monday, January 14, 2008 10:32 AM LearningExce wrote:

That's what learning is about.
Thanks a lot Ken Johnson, my question is answered.
Appreciate it.
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Learning Excel" wrote:


On Monday, January 14, 2008 11:20 AM RagDyeR wrote:

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

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Captain Jack Flak" wrote in
message ...
Thank you... this will work.

"Ragdyer" wrote:


On Monday, January 14, 2008 9:44 PM Ken Johnson wrote:

On Jan 14, 2:41 pm, Learning Excel
wrote:

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


On Monday, January 14, 2008 9:46 PM Ken Johnson wrote:

Hi Learning Excel

You're welcome.

Ken Johnson


On Monday, June 23, 2008 2:58 PM Bug Menot wrote:

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.


On Monday, June 23, 2008 4:16 PM Tim Zych wrote:

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: =

--
Tim Zych
www.higherdata.com
Compare data in worksheets and find differences with Workbook Compare
A free, powerful, flexible Excel utility


<Bug Menot wrote in message ...

[Olivier Beaujean]

This is very smart ! many thanks

On Sunday, January 13, 2008 7:38 PM Captain Jack Fla wrote:

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?


On Sunday, January 13, 2008 8:00 PM Tyro wrote:

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


On Sunday, January 13, 2008 8:05 PM TheRai wrote:

Just add $ in from of the leter or number or both.
A1relative $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.
--
Can''''t hear the Rain ?
Then listen to it.


"Captain Jack Flak" wrote:


On Sunday, January 13, 2008 8:08 PM Ragdyer wrote:

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 procedu
<Edit <Replace
In "Find What" enter
^^^
In "Replace With" enter the equal sign
=
Then <Replace All

All references remain unchanged.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Captain Jack Flak" <Captain Jack wrote in
message ...
in
absolute
aware


On Sunday, January 13, 2008 8:19 PM Tyro wrote:

The OP states:

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

Tyro


On Sunday, January 13, 2008 9:31 PM CaptainJackFla wrote:

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.

"Tyro" wrote:


On Sunday, January 13, 2008 9:31 PM CaptainJackFla wrote:

Thank you... this will work.

"Ragdyer" wrote:


On Sunday, January 13, 2008 10:13 PM LearningExce wrote:

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?
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Captain Jack Flak" wrote:


On Sunday, January 13, 2008 10:29 PM Mike06034 wrote:

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" wrote:


On Sunday, January 13, 2008 10:41 PM LearningExce wrote:

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...
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Mike Rogers" wrote:


On Sunday, January 13, 2008 10:51 PM LearningExce wrote:

Got to go. Hoping someone get the me good answer, will be appreciate it.
Thanks .
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Captain Jack Flak" wrote:


On Monday, January 14, 2008 10:32 AM LearningExce wrote:

That's what learning is about.
Thanks a lot Ken Johnson, my question is answered.
Appreciate it.
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Learning Excel" wrote:


On Monday, January 14, 2008 11:20 AM RagDyeR wrote:

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

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Captain Jack Flak" wrote in
message ...
Thank you... this will work.

"Ragdyer" wrote:


On Monday, January 14, 2008 9:44 PM Ken Johnson wrote:

On Jan 14, 2:41 pm, Learning Excel
wrote:

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


On Monday, January 14, 2008 9:46 PM Ken Johnson wrote:

Hi Learning Excel

You're welcome.

Ken Johnson


On Monday, June 23, 2008 2:58 PM Bug Menot wrote:

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.


On Monday, June 23, 2008 4:16 PM Tim Zych wrote:

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: =

--
Tim Zych
www.higherdata.com
Compare data in worksheets and find differences with Workbook Compare
A free, powerful, flexible Excel utility


<Bug Menot wrote in message ...


On Thursday, August 11, 2011 3:50 AM Olivier Beaujean wrote:

This is very smart ! thank you very much.