Using Tables with formulaes

[cyrilz]

Hello,


I'm using excel tables with formulaes in some column. The table looks like this (formula shown):

[pre]
A B C
1 AAA 1 =A1 & "-" & B1
2 AAA =A2 & "-" & B2
3 BBB 3 =A3 & "-" & B3


The table is defined with the range A1:C3. When I insert a line between rows 2 & 3 here's what I obtain :



A B C
1 AAA 1 =A1 & "-" & B1
2 AAA =A2 & "-" & B2
3 =A2 & "-" & B2
4 BBB 3 =A3 & "-" & B3
[/pre]
Which leads to incorrect results below the inserted line. :-(

Is there any way to avoid this behaviour ?


Regards


Cyril Z.

 

[Hui]

Cyril

When you say you have a Table is it a defined Table, as in Insert Table (Excel 07)

or is it just a Range of Cells that looks like a Table


When formated as a Table, there will be a Header Row, which can't have formulas

Inserting a Row in a Table and the Table automatically inserts the appropriate formulas


When formated as a range of cells, there doesn't have to be a header and you can have formulas, which looks like what you've got

Inserting a Row in this case should give a blank row


I tried both and can't reproduce your problem

 

[cyrilz]

Hui, it is a 2007 Table (Insert Table). I've not repoduced the header in the post, but you're right there are here.


I've selected the row, and right click > Insert line.


Regards.

 

[cyrilz]

Oops, it seems I've made a mistake.

The real behaviour is that the line below the insertion maintains its dependencies :

[pre]
A B C
1 AAA 1 1
2 AAA =B2 - C1
3 =B3 - C2 ==> Correct, but the next line isn't changed.
4 BBB 3 =B4 - C2 ==> Reference to the previous referred line.
5 DDD 4 =B5 - C4
[/pre]

This seems weird because the main benefit of Data Tables in Excel 2007 is that formulas are consistent through columns.


Any idea to avoid this, since I obviously wnanted to have =B4-C3 in line 4.


Regards