I have worksheet with several hundred formulas in the form of =E13. I want
to make all the formulas static, i.e. =$E$13, because I have to delete some
rows and I don't want the formulas to automatically update to accomodate the
missing rows. Is there a way to do this in one fell swoop rather than
having to change them all manually?

Hi,
Highlight your sheet, press CTRL + H, find what enter =E13, replace with
enter =$E$13

"Bishop" wrote:

I have worksheet with several hundred formulas in the form of =E13. I want
to make all the formulas static, i.e. =$E$13, because I have to delete some
rows and I don't want the formulas to automatically update to accomodate the
missing rows. Is there a way to do this in one fell swoop rather than
having to change them all manually?

Here's a macro by Gord Dibben.

http://tinyurl.com/37rxk4

However, this may not work the way you think it will. The macro *will* make
the refs absolute but deleting rows/columns may cause the formulas to then
return #REF! errors. That's what happened when I tested it.

--
Biff
Microsoft Excel MVP


"Bishop" wrote in message
...
I have worksheet with several hundred formulas in the form of =E13. I want
to make all the formulas static, i.e. =$E$13, because I have to delete
some
rows and I don't want the formulas to automatically update to accomodate
the
missing rows. Is there a way to do this in one fell swoop rather than
having to change them all manually?

I think you need to explain what you intend to do in more detail. If
you have a formula like =E13 and this gets changed to =$E$13, then
what happens if you then delete row 13 ?

(Answer: you get #REF errors).

One possible way is to change all the formulae to text entries. You
can do this using Find & Replace (CTRL-H), and changing "=" to "zz=".
When you delete your rows these "formulae" will not change. After you
are done, you can apply Find & Replace again to change "zz=" back to
"=" and thus re-instate your formulae. However, you might end up with
other problems (like circular references).

Hope this helps.

Pete

On Feb 4, 6:04*pm, Bishop wrote:
I have worksheet with several hundred formulas in the form of =E13. *I want
to make all the formulas static, i.e. =$E$13, because I have to delete some
rows and I don't want the formulas to automatically update to accomodate the
missing rows. * Is there a way to do this in one fell swoop rather than
having to change them all manually?