Using Tables with formulaes « Chandoo.org - Learn Excel & Charting Online

Chandoo.org - Learn Excel & Charting Online - Forums » Ask an Excel Question

Using Tables with formulaes

(4 posts) (2 voices)

Started 3 years ago by Cyril
Latest reply from Cyril

Tags:

Cyril

Member
Joined: Jul '09
Posts: 82
Hello,

I'm using excel tables with formulaes in some column. The table looks like this (formula shown):
```
    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
```
Which leads to incorrect results below the inserted line. :-(
Is there any way to avoid this behaviour ?

Regards

Cyril Z.
Posted 3 years ago #
Hui

Excel Ninja
Joined: Jul '09
Posts: 5,025

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

Posted 3 years ago #
Cyril

Member
Joined: Jul '09
Posts: 82

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.

Posted 3 years ago #
Cyril

Member
Joined: Jul '09
Posts: 82
Oops, it seems I've made a mistake.
The real behaviour is that the line below the insertion maintains its dependencies :
```
    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
```
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
Posted 3 years ago #