Excel not updating cell references when inserting new rows

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I'm using a SUMIF/COUNTIF formula to average columns of
data based on criteria entered by the user. I have a
separate cell designated for the user to enter the
criteria. When I insert a new row (to add more data),
the cell references to the raw data being averaged do not
update. For example, my raw data ends on row 23, and the
row that contains the user's criteria is on row 25. When
I insert a new row after row 23, the cell references do
not update to now look at row 24 (the new row). I have
to edit the formula manually to pick up the new row of
data. Any ideas?
 
It won't necessarily do that (version dependent). You can in later versions of
Excel, choose Tools / Options / Edit / Check 'Extend Data Range Fomats and
Formulas' to accomplish this, but I don't like relying on that at all. Always
been a Belt and Braces kind of chap, so I prefer to always set a starting row
and a finishing row, colour them a different colour and half-size them. Put NO
data in them but have the formula start with the starting row and finish with
the finish row. Now as long as you click on either the last row (The coloured
one), or any row above it when you add a row, the formulas will all extend
automatically.

Example:-

A B C D E F........
1 Starting Row - Coloured and Half sized (Both optional but make it easier to
see)
2
3
4
5 Finishing Row - Coloured and Half sized (Both optional but make it easier to
see)

in say cell A6 have =SUM(A1:A5)

Click on row 5 or higher ( Not 1 though :-> ), and insert a row and the formula
will adjust automatically.
 
Hi Brian,

The ranges in your formulas will ONLY update if you insert a row
WITHIN the existing range.

Have you tried using a number GREATER than the last row of data, and
seeing if this has any effect on your results??


David


Explore and Enjoy
 
Brian said:
I'm using a SUMIF/COUNTIF formula to average columns of
data based on criteria entered by the user. I have a
separate cell designated for the user to enter the
criteria. When I insert a new row (to add more data),
the cell references to the raw data being averaged do not
update. For example, my raw data ends on row 23, and the
row that contains the user's criteria is on row 25. When
I insert a new row after row 23, the cell references do
not update to now look at row 24 (the new row). I have
to edit the formula manually to pick up the new row of
data. Any ideas?
Click to expand...

One way of tackling this is to write the formula using the offset function.
For example, suppose you have data in A1:A10 and a formula to calculate the
sum in A11. Instead of using the formula
=SUM(A1:A10)
which will not adjust if you insert a new row 10 for data, use
=SUM(A1:OFFSET(A11,-1,0))
 
Back
Top