Created attachment 35324 [details] test cases which demonstrate issue I am trying to implement cell copying algorithm for spreadsheet editor, and I ran into a problem with a test case. Consider following scenario : there is formula "A1-A2" in cell C3. Algorithm has to copy C3 to C2. Formula should become A0-A1, that is #REF!-A1. I use FormulaShifter class for adjusting of cell references, and I initialise it with createForRowCopy() method, setting both firstMovedRowIndex and lastMovedRowIndex parameters to 2, since that's index of row where C3 resides. Method adjustFormula() converts A1 to just A. I have tried a few other possibilities for initialisation of FormulaShifter instance (since there is no documentation, and I am not really sure how those parameters affect FormulaShifter), but all of them fail. So, if this is not a bug in FormulaShifter, please tell me what is correct way to initialise it. And also what is generally idea with ShiftMode.RowCopy value, that is, in what use cases should I use it. (Source code resides in attached file.)
Not sure if I understand the testcase correctly, but it looks like you have a formula "A1-A2", i.e. row 0, cell 1 and 2 and you are shifting row 2 up by one row, i.e. row 2 (C) onto row 1 (B). Then it seems correct to me that the formula for row 0 (A) is kept in-tact, or? Maybe you can reduce your test down to the minimum, see the information at https://stackoverflow.com/help/mcve for some hints about that.
Created attachment 35334 [details] minimized version of test case
Created attachment 35335 [details] test file, for manual work in Excel
Ok, I have simplified test case a bit (see attachment 35334 [details]), now it does not include variations on parameter values, and formula is now as trivial as it can be. "A" is reference to column, not row. A1 is [row0, cell0], as you can see in my source code : sheet.createRow(0).createCell(0).setCellValue(1); // A1 I am copying from row2 to row1, but these are not C and B (again, letters stand for column references), these are rows marked as 3 and 2 in common spreadsheet editors. Further, that means that cell reference from formula should shift one row up, that is, A1 becomes A0 (which is undefined, and excel writes it as "#REF!"). You can check this if you open attached xlsx file (attachment 35335 [details]) in spreadsheet editor, and copy cell C3 to C2. To resume : - if you open atached file rowShiftExample.xlsx in Excel, and manually copy C3 to C2, you'll get value #REF! In C2; - if you execute attached application (attachment 35334 [details]) which should do the same thing, you'll get result “A” in variable shiftedFmla. I hope it is more clear now.
doh, sorry, must have been asleep already to mix up "A" vs. row.
Thanks, now it was much easier to reproduce and thus fix, see r1809967 for the actual changes.