Working with formulas and FlexCell Reports


In one excel file I have one main worksheet where I show some data and charts.
In a secondary worksheet I load data using the FlexCel Reports.

For example I have at first working sheet one cell with the formula 

But after loading for example 300 rows, the formula at main sheet looks for 

I tried many things which I thought it would work, but none of them worked and at moment as a workaround I'm generating the file, then opening the same file with FlexCell and setting the formula again.
using: <#formula>=Sheet2!A3+Sheet2!B3
Maybe this does not work as the first worksheet is processed first?

using fixed: =Sheet2!$A$3+Sheet2!$B$3


Could you please let me know how this could work?

Indeed, solution 1) won't work because Sheet1 is processed first, and so <#formula>=Sheet2!A3+Sheet2!B3
is replaced by
after Sheet1 is finished. When you insert rows in Sheet2, the formula in Sheet1 will change.

Solution 2) won't work either, because $ is not for inserting, but for copying. You can try it in Excel too. Create a file with 2 sheets. Write =Sheet2!$A$2 in Sheet1:

Then insert some rows in Sheet2:

You'll see Sheet1 formula will change:

And if you think about it, it makes sense. You were originally referencing the cell with "Hello" in Sheet2, then you moved that cell down. You need to then modify the formula in Sheet1 so it still references the cell with "Hello". Both FlexCel and Excel behave this way, and always adapt formulas when you move referenced cells, even if you use a $. The $ is only for copying, so if you copy the formula in A1 to A2, it will still be $A$7 in A2. But if you move A2, all formulas that reference A2 will move to reference the moved cell, no matter if you used $ or not.

Now, about the solution. If you want a fast hack, you can use a formula like:
This formula will always reference Sheet2!A2 no matter where it is moved, because it is just a string.
There are other ways you could keep the formula "fixed" to A2, but I wonder if the issue to fix isn't a different one. As you can see in the screenshots above, if you insert cells, you normally want the reference to move. If Sheet1!a1 was pointing to Sheet2!A2 (so the result was "hello") and you move Sheet2!A2, you want Sheet1!A1 to change, so it still keeps saying "hello".

If in your template you are referencing sheet2!a3, and it moves, is because the report in sheet2 moved A3, and what you probably want to do is not move A3 in the first place. It is hard to say what the real solution would be without seeing the template you are using, but normally:
1)If you are inserting cells from say columns B to C in sheet2, so column A is fixed: You can use a range instead of a range.  a range will insert cells only in columns B and C, and not move A.

2)If the idea is not to move stuff at all, you can use a _FIXED range so cells in sheet 2 are not inserted, but overwritten.

The idea is that if you want to reference sheet2!a3, you shouldn't move it when you run the report. If it moves, it only makes sense that the references in all other sheets to Sheet2!A3 move too.

But as said, it is hard to say more without seeing the template. If you want, you can email it to me to and I can take a look to see if I am missing things.

Hi Adrian,

Thank you for your great support and great library.

The INDIRECT solved my problem. I never used this INDIRECT function from excel before.

Kind regards