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