Formulas containing 'R' column cells being re-written with '$'

[scott6770]

I have a worksheet that has been working perfectly for a few years. We just upgraded to 4.5.3.3 and now SUM formulas that contain a reference to a cell in column 'R' are being modified at some point in the writing of the spreadsheet.

I'm using Worksheet.Cells(Row, Column).Address to get the values for my SUM ranges. However, in testing, I'm just hard-coding the values and the same behavior occurs.

Examples:
sum(R16:R35) => sum($16:$35) when I write to the formula of the cell
sum(S16:S35) is correctly written
sum(R16:S16) => causes error and excel removes from spreadsheet. Assuming it is written as sum($16:S16)
sum(Q16:S16) is correctly written
Average(R16:R35) => Average($16:$35)

If I write my formulas to the value property of the cell it is written correctly.

What is so magical about the value of 'R'? Can I escape the value so it works? I cannot exclude that column because our data might required that we use that column.

Looks like our option at this time to roll back to the previous version?

[JanKallman]

Revert for now. I'll have a look if the same behavior exists in the new codebase for EPPlus 5. My guess is that this is due to a pull request for the R1C1 handling that we merged in some time ago.
It has been rewritten, but I'll verify that it works.

[scott6770]

Thank you

[JanKallman]

I have a hard time reproducing this issue. Can you provide some code that reproduces this behavior?

[scott6770]

Sure, but before I do. After looking at the common code where we set the formula (and a bunch of other options), we are using the "FormulaR1C1" property. So I would guess that is the cause of the problems based on your comment above.

Do you recommend switching this to the "Formula" property to see if that resolves the issue?

[scott6770]

I'm going to close this as it was because we were assigning the formula to the FormulaR1C1 property, but were using the actual cell address instead of the Row and Column numbers.