Hi all,
I have this formula:
=IF(Z$30="Real";GL("Balance";$E32;Z$27;Z$28;;;;;;;;$J$3);
IF(AND(Z$30="Budget";$E32<>"97310");GL("Budget";$E32;Z$27;Z$28;;;;;;;$J$15;$J$3);
IF(AND(Z$30="Budget";$E32="97310");SUMIFS(Z:Z;$F:$F;"<>CIT";$G:$G;"Income Statement")*(CIT_PERC/100)*-1;0)))
This formula calculates just fine however, I would like to put the formula "SUMIFS(Z:Z;$F:$F;"<>CIT";$G:$G;"Income Statement")*(CIT_PERC/100)*-1" in the cell instead of the resulting value. This because of the fact the sheet gives the user a starting point in terms of real and budget figures from Navision and the user will start changing figures to create a new forecast (budget scenario) in wich this cell should recalculate based on changed values.
Does anyone know if this is possible (put the formula in a cell instead of the resulting value)?
Thank you very much in advance!!!
Best regards,
Gerard
Date
Votes
6 comments
-
Jet Reports Historic Posts Hi Gerard,
Yes it's certainly possible to put a formula in a cell. You just need to property quote it in your formula like this:=IF(Z$30="Real";GL("Balance";$E32;Z$27;Z$28;;;;;;;;$J$3);
IF(AND(Z$30="Budget";$E32<>"97310");GL("Budget";$E32;Z$27;Z$28;;;;;;;$J$15;$J$3);
IF(AND(Z$30="Budget";$E32="97310");"SUMIFS(Z:Z;$F:$F;""<>CIT"";$G:$G;""Income Statement"")*(CIT_PERC/100)*-1";0)))
Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
Thanks for your help! This partly solved the issue.
What happens is that the formula is placed in the cells, depending on the conditions of course. However it is not validated as a formula during the Jet-Refresh procedure. After completing the Jet-Refresh procedure it is necessary to re-edit the cell (<F2>+<Enter>) to force calculate the formula.
Because of this the autofit of the column is triggered by the length of the formula (syntax) and not by the result of it.
I hope you understand what I trying to say?
Best regards,
Gerard -
Jet Reports Historic Posts Hi Gerard,
Okay I think I see the problem. You don't actually want the formula to show up instead of the value in the cell. You just don't want the formula to get converted to only a value when the Jet formulas get converted to values. Basically, it seems like the best thing to do would be to put your GL(Balance) and GL(Budget) formulas in their own cells. Then when the Jet formulas get converted to values, your formula won't get converted because it only references Jet formulas. So say, your Jet formulas were in X30 and Y30, then your formula might look like this:=IF(Z$30="Real";X30;IF(AND(Z$30="Budget";$E32<>"97310");Y30;IF(AND(Z$30="Budget";$E32="97310");SUMIFS(Z:Z;$F:$F;"<>CIT";$G:$G;"Income Statement")*(CIT_PERC/100)*-1;0)))
This way, since this cell doesn't have any Jet formulas in it, it would not get converted to a value, so after running the report, the cell should still have the formula in it. Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
Thanks for your repsonse.
What you suggest, should solve the problem. However, due to the complexity of the report I don't think this is the solution for me. Sorry for that.
I can upload this report using a ticket, if you would be so kind to take a look at it?
Best regards,
Gerard -
Jet Reports Historic Posts If you create a support ticket, I'm sure our support staff will look at it. I am a developer so I wouldn't look at it unless it turned out to be an issue that needed to be fixed in the Jet software, which seems unlikely. But you can definitely create a support ticket, and someone should be able to help you with it.
Regards,
Hughes -
Jet Reports Historic Posts I created a support ticket and attached the report.
Thanks for your help so far!
Best regards,
Gerard
Please sign in to leave a comment.