R1C1 Formula Notation References Wrong Column Number

[austingriff]

When using R1C1 notation, EPPlus transforms the formula and references the original column number minus one. For example, if column in original formula references 3 then EPPlus references column 2. If original formula references column -2, then EPPlus references column -3.

(1) Below we can see the formula from my original excel template: =IFERROR( INDEX(ResourceCost, MATCH([@Level]&[@[Resource Office]],ResourceCost[Level]&ResourceCost[Office],0),3), 0)

(2) EPPlus transforms the above formula as: =IF(ISNA(VLOOKUP(B16,ResourceCost,2)), 0, VLOOKUP(B16,ResourceCost,2))

(3) We can see it is referencing 2 when it should reference 3. Manually changing to correct column via excel "fixes" the issue. EPPlus should transform (1) as follows: =IF(ISNA(VLOOKUP(B16,ResourceCost,2)), 0, VLOOKUP(B16,ResourceCost,2))

Note: ResouceCost, Level, Resource Office are named values i.e. ranges/table/array.

[austingriff]

This issue has actually been resolved in original repository by JanKallman. Is there any way this package can be updated with the fix?

https://github.com/JanKallman/EPPlus/pull/22

Just need to merge commit 'db8d99b' from JanKallman with ExcelCellBase.cs

[VahidN]

Should we merge a PR which is not merged in the main repo yet?

[austingriff]

Yes, the pull request from original repository is here: https://github.com/JanKallman/EPPlus/pull/22

Or could you create a new "dev" branch and merge the PR to it so I can fork the branch and use the fix? First option is preferred, but any help is appreciated.

[VahidN]

I'm not going to do that. when a PR is not merged yet, it means its quality has issues or it's not evaluated yet. So just wait until it's final.

[austingriff]

okay, thanks