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

On Nov 2, 9:36 pm, KelliInCali
wrote:
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?

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



"Pete_UK" wrote:

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

On Nov 2, 9:36 pm, KelliInCali
wrote:
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?

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.


"KelliInCali" wrote in message
...
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



"Pete_UK" wrote:

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

On Nov 2, 9:36 pm, KelliInCali
wrote:
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?

If you just add a row at the end of a table then Excel will not adjust
its formulas - the trick is to Insert a row within the table.

Alternatively, you might like to use dynamic ranges, which
automatically adjust to suit the amount of data you have. If you want
to take this route, here's a couple of references you might like to
study:

http://www.contextures.com/xlNames01.html#Dynamic

http://www.cpearson.com/excel/named.htm#Dynamic

Hope this helps.

Pete

On Nov 5, 4:33 pm, KelliInCali
wrote:
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



"Pete_UK" wrote:
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

On Nov 2, 9:36 pm, KelliInCali
wrote:
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?- Hide quoted text -

- Show quoted text -

=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


On Mon, 5 Nov 2007 08:33:06 -0800, KelliInCali
wrote:

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



"Pete_UK" wrote:

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

On Nov 2, 9:36 pm, KelliInCali
wrote:
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?