data and formulas sheet: sort gives incorrect cell-relations

Calc sheet data and formulas:

- Code: Select all Expand viewCollapse view
`! ! A ! B ! C ! D ! E ! F !`

+---+-----------------+----------+-------+----------+-------+-----------------+

! 1 !=C1&" "&B1 !FirstName !LstNme !Friend-Nm !=C1 !=E1&" "&D1 !

+---+-----------------+----------+-------+----------+-------+-----------------+

! 2 !=C2&" "&B2 !Paul-Anne !Paulus !=B3 !=C3 !=E2&" "&D2 !

! 3 !=C3&" "&B3 !Anne-John !Anders !=B4 !=C4 !=E3&" "&D3 !

! 4 !=C4&" "&B4 !John-Paul !Johnes !=B2 !=C2 !=E4&" "&D4 !

+---+-----------------+----------+-------+----------+-------+-----------------+

Calc sheet data and formulas, unsorted result:

- Code: Select all Expand viewCollapse view
`! ! A ! B ! C ! D ! E ! F !`

+---+-----------------+----------+-------+----------+-------+-----------------+

! 1 !LstNme FirstName !FirstName !LstNme !Friend-Nm !LstNme !LstNme Friend-Nm !

+---+-----------------+----------+-------+----------+-------+-----------------+

! 2 !Paulus Paul-Anne !Paul-Anne !Paulus !Anne-John !Anders !Anders Anne-John !

! 3 !Anders Anne-John !Anne-John !Anders !John-Paul !Johnes !Johnes John-Paul !

! 4 !Johnes John-Paul !John-Paul !Johnes !Paul-Anne !Paulus !Paulus Paul-Anne !

+---+-----------------+----------+-------+----------+-------+-----------------+

Calc sheet data and formulas, sort results in incorrect cell-relationship,

(standing in cell C1, sorted on column C, containing column headings):

- Code: Select all Expand viewCollapse view
`! ! A ! B ! C ! D ! E ! F !`

+---+-----------------+----------+-------+----------+-------+-----------------+

! 1 !=C1&" "&B1 !FirstName !LstNme !Friend-Nm !=C1 !=E1&" "&D1 !

+---+-----------------+----------+-------+----------+-------+-----------------+

! 2 !=C2&" "&B2 !Anne-John !Anders !=B3 !=C3 !=E2&" "&D2 !

! 3 !=C3&" "&B3 !John-Paul !Johnes !=B1 !=C1 !=E3&" "&D3 !

! 4 !=C4&" "&B4 !Paul-Anne !Paulus ! =B5! =C5!=E4&" "&D4 !

+---+-----------------+----------+-------+----------+-------+-----------------+

Calc sheet data and formulas, sorted incorrect result:

- Code: Select all Expand viewCollapse view
`! ! A ! B ! C ! D ! E ! F !`

+---+-----------------+----------+-------+----------+-------+-----------------+

! 1 !LstNme FirstName !FirstName !LstNme !Friend-Nm !LstNme !LstNme Friend-Nm !

+---+-----------------+----------+-------+----------+-------+-----------------+

! 2 !Anders Anne-John !Anne-John !Anders !John-Paul !Johnes !Johnes John-Paul !

! 3 !Johnes John-Paul !John-Paul !Johnes !FirstName !LstNme !LstNme FirstName !

! 4 !Paulus Paul-Anne !Paul-Anne !Paulus ! 0! 0!0 0 !

+---+-----------------+----------+-------+----------+-------+-----------------+

cell D3 should contain B4 (not B1) and cell E3 should contain C4 (not C1)

cell D4 should contain B2 (not B5) and cell E4 should contain C2 (not C5)

How to get a correct sorted result without using fixed $ cells (then 'insert row' should give wrong results)?

Calc sheet data and formulas, with absolute references,

amazing but sort results also in incorrect cell-relationship,

(standing in cell C1, sorted on column C, containing column headings):

- Code: Select all Expand viewCollapse view
`! ! A ! B ! C ! D ! E ! F !`

+---+-----------------+----------+-------+----------+-------+-----------------+

! 1 !=C1&" "&B1 !FirstName !LstNme !Friend-Nm !=C1 !=E1&" "&D1 !

+---+-----------------+----------+-------+----------+-------+-----------------+

! 2 !=C2&" "&B2 !Paul-Anne !Paulus !=B$3 !=C$3 !=E2&" "&D2 !

! 3 !=C3&" "&B3 !Anne-John !Anders !=B$4 !=C$4 !=E3&" "&D3 !

! 4 !=C4&" "&B4 !John-Paul !Johnes !=B$2 !=C$2 !=E4&" "&D4 !

+---+-----------------+----------+-------+----------+-------+-----------------+

Calc sheet data and formulas with absolutereferences, also sorted incorrect result:

- Code: Select all Expand viewCollapse view
`! ! A ! B ! C ! D ! E ! F !`

+---+-----------------+----------+-------+----------+-------+-----------------+

! 1 !LstNme FirstName !FirstName !LstNme !Friend-Nm !LstNme !LstNme Friend-Nm !

+---+-----------------+----------+-------+----------+-------+-----------------+

! 2 !Anders Anne-John !Anne-John !Anders !Paul-Anne !Paulus !Paulus Paul-Anne !

! 3 !Johnes John-Paul !John-Paul !Johnes !Anne-John !Anders !Anders Anne-John !

! 4 !Paulus Paul-Anne !Paul-Anne !Paulus !John-Paul !Johnes !Johnes John-Paul !

+---+-----------------+----------+-------+----------+-------+-----------------+

these absolute references are not that absolute

- Code: Select all Expand viewCollapse view
`! ! A ! B ! C ! D ! E ! F !`

+---+-----------------+----------+-------+----------+-------+-----------------+

! 1 !=C1&" "&B1 !FirstName !LstNme !Friend-Nm !=C1 !=E1&" "&D1 !

+---+-----------------+----------+-------+----------+-------+-----------------+

! 2 !=C2&" "&B2 !Anne-John !Anders !=B$4 !=C$4 !=E2&" "&D2 !

! 3 !=C3&" "&B3 !John-Paul !Johnes !=B$2 !=C$2 !=E3&" "&D3 !

! 4 !=C4&" "&B4 !Paul-Anne !Paulus !=B$3 !=C$3 !=E4&" "&D4 !

+---+-----------------+----------+-------+----------+-------+-----------------+

cell D2 should contain B$3 (not B$4) and cell E2 should contain C$3 (not C$4)

cell D3 should contain B$4 (not B$2) and cell E3 should contain C$4 (not C$2)

cell D4 should contain B$2 (not B$3) and cell E4 should contain C$2 (not C$3)

CONCLUSION:

sort does not work in sheets containing vertical cell-references

(same problem in MS-Office Excel as in Open Office Calc)