If you have a cell with a formula with some references (eg. "=(B5-C5)/B5") and you shift the row with Sheet.shiftRows(int,int,int) (eg. sheet.shiftRows(4,4,-1)) you lose the references and get "=(#REF!-#REF!)/#REF!").

What version of Apache POI are you using? If it isn't the latest, what happens when you upgrade? (We've done quite a few fixes in this area lately)

I'm using 3.17. It's the latest if I'm not mistaken. While I'm here I add the following issue: please make sure that references contained in the formula are updated when rows are shifted (same as when you copy paste formulas in Excel) and also when cells are copied. Because now, if you copy paste a formula from a cell into another cell with cell.setCellType(CellType.FORMULA); setCellFormula(anotherCell.getCellFormula()); formula is treated as a mere string and references are not updated.

This works for me in the latest version of POI (and probably also 3.17). You will get #REF! errors if you shift references above the 1st row in the workbook, which is the same behavior as Excel. You are getting #REF! errors likely from one of two scenarios: 1) Formula: "(B1-C1)/B1", shiftRows(x, x, -1) 2) Formula: "(B5-C5)/B5", shiftRows(x, x, -5) I wrote this unit test to test POI's functionality: @Test public void test61840_shifting_rows_up_does_not_produce_REF_errors() throws Exception { Workbook wb = _testDataProvider.createWorkbook(); Sheet sheet = wb.createSheet(); Cell cell = sheet.createRow(4).createCell(0); cell.setCellFormula("(B5-C5)/B5"); sheet.shiftRows(4, 4, -1); Cell shiftedCell = sheet.getRow(3).getCell(0); assertNotNull(shiftedCell); assertEquals("(B4-C4)/B4", shiftedCell.getCellFormula()); }

(In reply to Luca from comment #2) > setCellFormula(anotherCell.getCellFormula()); formula is treated as a mere > string and references are not updated. setCellFormula intentionally does not shift the formula that is pasted into it. It sets the cell formula as is. Perhaps what you're looking for is a function that copies a cell value from another cell, and if the source cell contains a formula, to shift that formula. Currently POI only contains methods to copy entire rows (Sheet.copyRows). Adding capability for copying just a cell is a good idea, and should be captured on a new bug.

Added a unit test in r1816892.