stavros_mrg Posted April 29, 2023 Share Posted April 29, 2023 Hello Everyone, I am trying to export the following formula in a cell of a paginated .XLSX file. However, when i export the report and then try to open the .xlsx file, that comes with errors and the pages which contain the formula are blank. The strange thing is that when i export to .XLS the formula is placed properly. The formula mentioned is this =SUMPRODUCT(SUMIF(INDIRECT("'"&$K$3:INDIRECT(ADDRESS(3,COLUMN()+J3))&"'!"&"K:K"),C6,INDIRECT("'"&$K$3:INDIRECT(ADDRESS(3,COLUMN()+J3))&"'!"&"I:I"))). It does make the sumif from multiple sheets of the workbook. Any ideas? Link to comment Share on other sites More sharing options...
Mehak Rajkumar Posted May 2, 2023 Share Posted May 2, 2023 Thank you for posting to the Jaspersoft Community. Our team of experts has read your question and we are working to get you an answer as quickly as we can. If you have a Jaspersoft Professional Subscription plan, please visit https://support.tibco.com/s/ for direct access to our technical support teams offering guaranteed response times. Link to comment Share on other sites More sharing options...
Jivan Phadtare Posted May 3, 2023 Share Posted May 3, 2023 Hello,Not sure what exact error you are getting while exporitng into .xlsx format.But seems that for .xlsx format you need to change the formula.The formula for .xls and .xlsx is slightly differs that why for .xlsx format you might be getting blank.Try using & instead of & in formula to export in .xlsx format.For more details you can refer to below article.https://community.jaspersoft.com/wiki/exporting-crosstab-formula-sum-excelHope this will help you. Link to comment Share on other sites More sharing options...
stavros_mrg Posted May 3, 2023 Author Share Posted May 3, 2023 You are a genius! The replacement of & with & worked!However, it doesnt calculate at once. I have to Ctrl+X the formula and paste it in the formula bar again in order it returns results. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now