Inserting rows without changing formula values
 

How do I keep the value range constant in a formula when I insert new rows?
I have a constant value, 1000 and I need to subtract the value in cell B-3
daily. I have the formula set up as =SUM(1000-B3). Whenever I insert a row,
the formula adjusts B3 to B4 - I need for it to stay B3 even after inserting
new rows.

Inserting rows without changing formula values
 

Dollar sign make a location static:

--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.
=SUM(1000-$B$3)

"Hook''em 2006" wrote:

How do I keep the value range constant in a formula when I insert new rows?
I have a constant value, 1000 and I need to subtract the value in cell B-3
daily. I have the formula set up as =SUM(1000-B3). Whenever I insert a row,
the formula adjusts B3 to B4 - I need for it to stay B3 even after inserting
new rows.

Inserting rows without changing formula values
 

Try this:

=1000-INDIRECT("B3")

--

HTH,

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

"Hook''em 2006" wrote in message
...
How do I keep the value range constant in a formula when I insert new rows?
I have a constant value, 1000 and I need to subtract the value in cell B-3
daily. I have the formula set up as =SUM(1000-B3). Whenever I insert a
row,
the formula adjusts B3 to B4 - I need for it to stay B3 even after inserting
new rows.

Inserting rows without changing formula values
 

Change your formula to:
=1000-$B$3
HTH Otto
"Hook''em 2006" wrote in message
...
How do I keep the value range constant in a formula when I insert new
rows?
I have a constant value, 1000 and I need to subtract the value in cell B-3
daily. I have the formula set up as =SUM(1000-B3). Whenever I insert a
row,
the formula adjusts B3 to B4 - I need for it to stay B3 even after
inserting
new rows.

Inserting rows without changing formula values
 

Have you looked at what happens to that formula if you insert a new row
before row 3, Otto?

The INDIRECT() solution suggested in an earlier reply makes sense.
--
David Biddulph

"Otto Moehrbach" wrote in message
...
Change your formula to:
=1000-$B$3
HTH Otto

"Hook''em 2006" wrote in message
...
How do I keep the value range constant in a formula when I insert new
rows?
I have a constant value, 1000 and I need to subtract the value in cell
B-3
daily. I have the formula set up as =SUM(1000-B3). Whenever I insert a
row,
the formula adjusts B3 to B4 - I need for it to stay B3 even after
inserting
new rows.

Inserting rows without changing formula values
 

David
That's a new one on me. Thanks for that. Otto
"David Biddulph" wrote in message
...
Have you looked at what happens to that formula if you insert a new row
before row 3, Otto?

The INDIRECT() solution suggested in an earlier reply makes sense.
--
David Biddulph

"Otto Moehrbach" wrote in message
...
Change your formula to:
=1000-$B$3
HTH Otto

"Hook''em 2006" wrote in message
...
How do I keep the value range constant in a formula when I insert new
rows?
I have a constant value, 1000 and I need to subtract the value in cell
B-3
daily. I have the formula set up as =SUM(1000-B3). Whenever I insert a
row,
the formula adjusts B3 to B4 - I need for it to stay B3 even after
inserting
new rows.