Created attachment 28856 [details] Clone formula Shift a formula that contains absolute cells references with method adjustFormula change abosulte rows references.
Created attachment 28857 [details] Template excel Template excel. Cell A1 contains formula : =SI($C1=O$1;SI(STXT($F1;1;1)="6";$H1-$I1;$I1-$H1);0) After copy and shitfing formula from cell A1 to cell A2, we have: =SI($C2=O$2;SI(STXT($F2;1;1)="6";$H2-$I2;$I2-$H2);0) instead of =SI($C2=O$1;SI(STXT($F2;1;1)="6";$H2-$I2;$I2-$H2);0)
Adding test on isRowRelative() in rowMoveRefPtg (in FormulaShifter) seems toworks. private Ptg rowMoveRefPtg(RefPtgBase rptg) { int refRow = rptg.getRow(); if (_firstMovedIndex <= refRow && refRow <= _lastMovedIndex) { // Rows being moved completely enclose the ref. // - move the area ref along with the rows regardless of destination if (rptg.isRowRelative()) rptg.setRow(refRow + _amountToMove); return rptg; } ....
I think this is fixed in POI 3.13, if not earlier. I'm using XSSFSheet.shiftRows and formulas with absolute row references are getting adjusted, if my memory serves correctly.
This was fixed for row copy but not row move (Sheet.shiftrows) https://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/FormulaShifter.java?revision=1722410&view=markup
This behavior may be by design. Shift rows isn't the same as insert rows. I'm not sure if it's the same as cut-paste, either. I'd be happy to discuss this on the dev mailing list.