Offset and Repeating Dynamic Formula

Aspose.Cells Product Family
1

I have tried using the offset excel formula in an Excel designer document. I have set it using a repeating dynamic formula. I get an exception stating that it is an invalid formula. After researching the problem I have found out that this formula is not supported.

This a big problem for us. Are there plans to support this formula?

Is the lack of support for the offset formula and others because of the Formula Calculation Engine? If so, is it possible that Aspose.Excel can be run in a non-Formula Calculation Engine mode to allow any formula? I don’t think that I will ever have the need to use the calculation engine.

Ultimately I would like to output any formula to my excel document without Aspose.Excel throwing and exception.

Many Thanks,
Matt

2

Hi Matt,

Please try this attached fix.

3

Thank you for your fast reply! When I have a chance later today I’ll give this a try and report back.

Thanks Again,
Matt

4

Still no luck. I tried this repeating dynamic formula, &=&=OFFSET(C3,2,3,1,1).

I will need to reference cells in another sheet but I tried the simple test above.

Thanks,
Matt

5

Hi Matt,

Please try the attached fix.

6

Laurence,

We are almost there. The formulas are being output to my Excel document. The problem I’m having now is that Excel is not doing an evaluation of the formulas when I open the document in Excel. I get a #NAME? or #VALUE! error when OFFSET is used. If I select the cell with the error, place my cursor at the end of the formula in the formula bar and press enter the formulas in their original format work.

I have tried forcing Excel to recalculate all formulas using the many variations of Alt+Ctrl+Shift+F9.

Is there something more low level that is being reset when I fake the update of a cell’s formula?

Let me know if there is anything else I can provide.

Thanks,
Matt

7

Could you please upload your generated file here? That will help me to figure out this problem. Thank you.

8

Here are the results.

Thanks,
Matt

9

Hi Matt,

Could you please also upload your template file here? Thank you.

10

Here you go.

Thanks,
matt

11

Hi Matt,

This issue is caused by that “Match” function is not supported in earlier version. I added it in the new fix. Please try it.

12

Laurence,

I finally had an opportunity to test the latest version. It is working correctly now.

Aspose is now one of my favorite companies. I have never experienced customer service like I have with Aspose. I know that we could all learn from Aspose’s leadership in customer service.

You guys are awesome!

Many Thanks and Many Cheers,
Matt