I am exporting 6 fields into excel. I then use an excel formula in column 7 to carry out a calculation on columns 5 & 6.
I have been entering the formula while reformatting the output of a report and saving back to the template.
I have done some testing on this and it seems to be the formula is not being written back to the template for every row
I don't see the problem if I edit the template directly from manage templates.
Saving back to the template from the report itself is an ease-of-use type feature. It does not allow some changes to be saved because it removes the data before saving back to the template. If you write to the data area, that information is lost as far as the template is concerned. The Excel options "Data Write Method" Insert or Direct also effects the way data is handled when saving back to template directly from a generated report. If you want to specify the formatting for a whole column, select the default whole column and format it appropriately. You can then set any other special formatting above and below the data area.
What you should really be doing is adding an excel formula field using the Txt/fx option in the select fields dialog box. You need to use the R1C1 excel reference method to refer to cells. R1C1 is documented in Excel help. It provides a way to reference cells by absolute position or offsets. This may seem difficult but once you understand how to use it you will find it easy to work with. Actually it doesn’t make sense to do it any other way. The A1, A2 type reference can not specify offsets directly.
See the example I have included (Column 8). R[0] refers to the current row. It can also be specified as R. C1 specifies column 1. The current column would be C. The previous column from the current column would be C[-1]. To refer to the previous column and previous row you would use R[-1]C[-1].
008 “=Text(R[0]C1)”
Column 8 here is taking a value from colum 1 in the same row and converting it to text
