Ask Your Question

Revision history [back]

click to hide/show revision 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.

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.