Prev: Stoping Excel from Calculating?
Next: How do I force the y-intercept value
From: Bishop on 4 Feb 2010 13:04
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?
From: Eduardo on 4 Feb 2010 13:10
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?
From: Pete_UK on 4 Feb 2010 13:58
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 <Bis...(a)discussions.microsoft.com> 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?

 | 
Pages: 1
Prev: Stoping Excel from Calculating?
Next: How do I force the y-intercept value