relative formula not changing

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a formula that is outside of a list (sorry... TABLE in 2007). It is
set to total a column which spans the rows of two separate lists. The
formula range is not fixed, but the row # is not increasing when either of
the the lists auto-add rows. It always worked with the old version. Any
help?
 
Is your sheet set for manual calculation? Press F9 to see if this
recalculates, and if it does click on Tools | Options | Calculation
tab and click on Automatic.

Perhaps the format of the cell has been set to Text - click on Format
| Cells | Number tab to check, and set it to General. Double-click the
cell as if to edit it, then press <enter> to bring about the change in
format.

Hope this helps.

Pete
 
Thanks for the reply Pete. Yes, calc is set to auto. The formula is
operating, but it is not accounting for the additional rows when either of
the tables above it auto-inserts a new row. It is behaving as though it's
range is absolute when it's not. -kelli
 
I don't have Excel 2007, but a list is just a range of cells - let's say
A1:A9. Does your formula just refer to A1:A9 - that is, the exact rows of
your list? The point is that 'adding' a row (in my example, row 10) will not
change the range referred to, as this is outside the list.

What you would need to do is to alter the range in your formula to include
an extra row. So, for data in A1:A9 your formula would refer to A1:A10.
Then, when a new row is added (row 10), it will be within the table, so the
range will adjust to be A1:A11.
 
=SUM(A1:INDEX(A:A,ROW()-1))

As you insert rows above this formula it will expand to include those rows.

Or you could enter the formula in B1 and achieve the same thing.


Gord Dibben MS Excel MVP
 
Back
Top