Hi,
I'm using Jet Essential 15.0.15091.0, and trying to see how I can try to create a report similar to Account Schedule. Most of the functionality works as expected, but when I try to SUM multiple rows I'm lost in how I can manage this.
Is there a way to create a Formula, so that I can create calculation based on the Row No.?
Date
Votes
9 comments
-
Jet Reports Historic Posts Hi -
If you are attempting to sum up some values, the easiest method is to return the value directly from the database (if it is there… e.g., a pre-defined totaling account in the chart of account).
Otherwise, you could include the values you want in your worksheet and then use Excel's SUMIF function to work with them [e.g., =SUMIF(C5:C13,"CGS1",E5:E13)+SUMIF(C5:C13,"SGA1",E5:E13)]
If, however, you are attempting to build a string [e.g., 5800110|5990001|590002|5801310|5800410|685160], you would probably have to utilize Excel's string manipulation functions [e.g., contact, &]. This might include having to use Excel macros.
If you know which lines in your worksheet will contain the values you want, you could use the NP("Join") function to get you at least part of the way there.
-
Jet Reports Historic Posts Thank you for your reply.
From what I'm trying to prepare, seems like using the SUMIF would be the direction that I'd like to take.
But is there a way to convert a text string to a Formula provided by Jet Addin? I was trying something like below, but wasn't' successful. The reason why I'm trying to convert from text to Formula is because it is easier to create a logic in my design. -
Jet Reports Historic Posts Hello knm.
In my experience I have only seen the Jet Formula function work with tables and therefore probably not what you want here.
The function should work as long as there is an equal sign in the beginning to tell Excel that its a function. Looks like in you example you are missing that.
If you are still stuck, kindly attach the report you are using and a brief description of where you are stuck and we can continue to take a look. -
Jet Reports Historic Posts Hi,
Thank you so much for your response.
Attached is the kind of report I am working on. Let me know if anything is confusing, or whether I'm trying to achieve anything impossible. -
Jet Reports Historic Posts Hi,
I just wanted to follow-up if you can provide any additional feedback.
Thank you! -
Jet Reports Historic Posts Hello.
Can you please reattach. Not sure why, but when I download the file from your last post it says its corrupted.
Thanks. -
Jet Reports Historic Posts Hi,
I've replaced the file on my previous post. so would you please try again?
Thank you. -
Jet Reports Historic Posts Hello knm.
The attachment worked and I am able to open the report.
If you want to reference the formula in cell E, you could try the INDIRECT function. The Jet function =Formula() is only going to work when used in a Jet Table function.
I tried to rebuild the Account Schedule one time and attached are the results (This may not work on your database).
Homework 5 - Edit - Account Schedule - BALANCE SH ∙ Balance Sheet (DYNAMIC).xlsx
The easiest way to create this report is to just hard code the values based on the export of the report. Attached
Homework 5 - Edit - Account Schedule - BALANCE SH ∙ Balance Sheet (DYNAMIC).xlsx
I hope these examples help.
Best regards,
Jason -
Jet Reports Historic Posts Hi Jason,
Sorry for the late response. I tried to review the sample that you have provided, but it seems like I cannot find the "INDIRECT" function in the report that you have rebuilt. Would you please reupload the report that you have rebuilt using the INDIRECT method?
Thank you
knm
Please sign in to leave a comment.