This project has moved. For the latest updates, please go here.

Copying ranges with formulas using absolute references

Aug 4, 2014 at 11:06 PM
Hi

I have a template file that is populated from my database. So I insert a new row for each record of my DB and in this new rows I need to copy the formulas of the previous rows, some of these formulas have absolute references for example

=IF($D$33,U10/(1-W10),0)

and I would expect that on the next row the copied formula should be

=IF($D$33,U11/(1-W11),0)

but instead I'm getting

=IF($D$32,U11/(1-W11),0)
=IF($D$31,U12/(1-W12),0)


My code looks somethin like this
int prevRowNo = currentRowNo - 1;
var prevRow = oSheet.Row(prevRowNo);
prevRow.InsertRowsBelow(1);
               
//var currentRange = oSheet.Range("A" + setIdx.ToString(), "AZ" + setIdx.ToString());     
var PrevRange = oSheet.Range("$A$" + prevRowNo.ToString() + ":$AZ$" + prevRowNo.ToString());
oSheet.Cell(CurrentRowNo, "A").Value = PrevRange;
I don't know if I'm doing something wrong,

Thanks
Coordinator
Aug 6, 2014 at 5:28 PM
I need an easy way to reproduce the problem. The following works:
var wb = new XLWorkbook();
var ws = wb.AddWorksheet("Sheet");

ws.Cell("A1").SetValue(1)
    .CellBelow().SetFormulaA1("A1 + $A$1")
    .CopyTo("A3");
A1 = 1
A2 = "A1 + $A$1"
A3 = "A2 + $A$1"