Copy WITHOUT adjusting values

[MLE]

How do I copy a row WITHOUT the formulas that reference other rows changing?
E.g., I want to copy row 15 that references say row 10 to a new location, say
row 25. I want the new row to still reference row 10. When I use the
ordinary copy command the new row references row 20.
In my old spreadsheet program, Supercalc, I could choose to "adjust" or
"not adjust" values. How do I do that in Excel?

 

[Anirudh]

Dear MLE,

Pls write '$' sign before the ref: ie

=a$15 for freezing row 15
or
=$a$15 for freezing row and coloumn

anirudh

 

[Arvi Laanemets]

Hi

Select the row (the range with formulas);
From Edit menu, select Replace, and replace all '=" with '_=';
Copy edited range to new location;
Select both old and new ranges, and replace all '_=' with '='

 

[SteveH]

John Wakenbach has a neat little tool call the Exact Formula Copier in his
Power Utility Pack V6.
http://www.j-walk.com/ss/

It a lot less messier if you have a lot of rearranging to do.

Steve

 

[MLE]

I could not make your method work. Could you be more explicit about the
steps. I do not understand what you mean by '=" with '_='. Is the
difference between the quotation mark and the apostrophe important, or a typo?

 

[Gord Dibben]

MLE

Do not use any quotes marks at all when entering into the what: and with:
dialogs

just enter the = sign in the what: box and _= in the with: box.

Alternative in the with: box could be xxxx or some other unique string.

Arvi's quotes were there just to show the strings to enter an he just happened
to mix quote types.

The goal is to change the original formulas to strings, move them, then
re-change back to formulas.


Gord Dibben Excel MVP