Hi,
Indeed, solution 1) won't work because Sheet1 is processed first, and so <#formula>=Sheet2!A3+Sheet2!B3
is replaced by
=Sheet2!A3+Sheet2!B3
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:
=INDIRECT("Sheet2!A2")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
adrian@tmssoftware.com and I can take a look to see if I am missing things.
