First time here? Check out the FAQ!

1 | initial version |

Sample file in commented bug:

test.ods

If someone is interested in a workaround:

If the formula is inside sorted range a formula like: D4: =OFFSET(D4;-1;0)+(C4-B4) after sort: D3: =OFFSET(D3;-1;0)+(C3-B3) works fine for me in the sample file.

If the formula is outside sorted range a formula like: D4: =OFFSET(INDIRECT("A"&ROW());-1;COLUMN()-1)+OFFSET(INDIRECT("A"&ROW());0;COLUMN(C1)-1)-OFFSET(INDIRECT("A"&ROW());0;COLUMN(B1)-1) doesn't change their result after sort, because although row number in COLUMN() can change, formula equally works.

2 | No.2 Revision |

Sample file in commented bug:

test.ods

If someone is interested in a workaround:

If the formula is inside sorted range a formula ~~like:
~~like:

D4: ~~=OFFSET(D4;-1;0)+(C4-B4)
~~`=OFFSET(D4;-1;0)+(C4-B4)`

after ~~sort:
~~sort:

D3: ~~=OFFSET(D3;-1;0)+(C3-B3)
~~`=OFFSET(D3;-1;0)+(C3-B3)`

works fine for me in the sample file.

If the formula is outside sorted range a formula ~~like:
~~like:

D4: ~~=OFFSET(INDIRECT("A"&ROW());-1;COLUMN()-1)+OFFSET(INDIRECT("A"&ROW());0;COLUMN(C1)-1)-OFFSET(INDIRECT("A"&ROW());0;COLUMN(B1)-1)
~~`=OFFSET(INDIRECT("A"&ROW());-1;COLUMN()-1)+OFFSET(INDIRECT("A"&ROW());0;COLUMN(C1)-1)-OFFSET(INDIRECT("A"&ROW());0;COLUMN(B1)-1)`

doesn't change their result after sort, because although row number in COLUMN() can change, formula equally works.

Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.