Jasper report: use a complex excel formula

[karin]

I have a Jasper foundset report with 2 subreports. I want to print the total of a column from one of the subreports on the Page Footer. When I use the net.sf.jasperreports.export.xls.formula SUM(Q3 .. Q87) it works. But naturally I do not want to hard code the row number. I do not know how many rows my report has. I cannot use the $V{REPORT_COUNT} for the row number, as this only counts the records from the main report. But the 2 sub reports can both add 0 till multiple rows to the Excel spreadsheet.
I found a function in Excel which works. If I put the following formula on the total row SUM(Q3:INDIRECT("Q" & (ROW()-1))) it works. But when I send this information from my report to Excel, the correct value is not interpreted. The formula is correct, if I add a blank after the formula, Excel understands the formula and shows the correct information. But I should not have to change the formula for it to work.

Is there another solution for this? I do now want to calculate the total in Jasper Reports and print the total value. If the user deletes some rows from the excel spreadsheet the total will not be correct if I calculate the value in Jasper.