Hi Jackies,
I don't think it's actually possible to do this with an Excel formula the way you want to, which is why I'm trying to do it with a Jet formula. I think I also forgot a couple filters in my last formula, namely the filters for "Schedule Name" and "Totaling Type" on the Acc. Schedule Line in the linking part of the formula.
=IF(K7="","",IF(J7="Formula",NL("Sum","G/L Account","Balance at Date","Date Filter",Options!$D$8,"Account Type","Posting","company=",Options!$D$9,"Link=","Acc. Schedule Line","Totaling","=No.","Totaling Type","Formula","Schedule Name",Options!$D$13,"Row No.",NP("Split",K7,"+")),NL("Sum","G/L Account","Balance at Date","No.",K7,"Date Filter",Options!$D$8,"Account Type","Posting",,,,,,,,,,,,,"company=",Options!$D$9)))
Did you try this formula on your workbook? If this is not working, then maybe I just don't understand exactly what you're trying to do. I think this should be equivalent to doing the SUMIF function you were trying to do. Your SUMIF function is saying that you want to sum the values from the =NL("Sum","G/L Account"…) formulas where the value in column I (the Row No. from the Acc. Schedule Line) is one of the values contained in the current Totaling field (separated by a +). My formula is doing a single NL("Sum") from the G/L Account table where there exists a record on the Acc. Schedule Line table where the Totaling field matches a G/L account number and the Row No. is contained in the current Totaling field. I did forget a couple other filters in the last try for the Acc. Schedule Line table which may make a difference.
Since I can see that you are trying to sum multiple NL("Sum") formulas which are all going to the same table, we should be able to reproduce this by just doing a single NL("Sum") formula. There should be a way we can filter a single NL("Sum") formula which will give us the equivalent of the sum of other NL("Sum") formulas. Maybe I'm not quite understanding what you want, but I think this should be possible. That SUMIF formula will not work, so we must find a different way to make this work.
Regards,
Hughes
Date
Votes
19 comments
-
Jet Reports Historic Posts Official comment Found a way to make it work…AT LAST
1. Used =NL("Rows",NP("Split",K7,"+")) next to the Amount formula to split the Formula values (i.e. Cell N7)
2. Modified Subtotal formula in cell L11 to calculate different values when there is a formula in the line
P.S: It still has a long way to work dynamically because I have to split -,/ and * as well, but also perform these formulas… -
Jet Reports Historic Posts Hi All,
I'm building a report that extracts account schedules dynamically.
I have an NF function to record the G/L account number that sometimes is blank. I use another NL(Rows,) function based on the NF G/L account No. but I can't make it work:
1. If I use an IF statement to exclude the NL(Rows) function when the source cell is zero, I only get the one record in the NL(Rows) function
2. If I use "@@"& in the G/L Account No. filter for the NL(Rows), I get an Invalid function error when it reaches the line where the NF G/L account No. returns more than on G/L account No. (i.e.215102|214100|214101|214102..214104|215100|215101|300000..999990)
Thanks -
Jet Reports Historic Posts Hi Jackies,
You can shortcut the NL(Rows) function by putting a blank array in the table parameter. Your function would look something like this (assuming your NF function is in cell D4).
=NL("Rows",IF(D4="",{""},"G/L Entry"),,"G/L Account No.",D4,…)
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Thanks fhilton!
Works like a charm. :)
One more question though…
I want to add up to formula lines but I don't know how:
If it's a posting account, I calculate the G/L Account No. under Totaling field
If it's a formula that equals with a range of posting accounts, I use Excel SumIf formula
If it's a formula that equals to 2-3 other formula lines…I can't make it work. (see screenshot attached)
Thanks again for your useful tip :) -
Jet Reports Historic Posts I have attached the report for reference :)
-
Jet Reports Historic Posts Hi Jackies,
I haven't looked at your report, but I think you can use the NP(Split) function achieve your goal. NP(Split) will take a string and split it into an array of values based on a split character. In your case, you could split it based on the + and then use the array as a filter for the Row No. field. It might look something like this:
=NL("Sum","G/L Account","Balance","Row No.",NP("Split",NF(C4,"Totaling"),"+"))
Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Thanks fhilton for your help.
However, I can't use Split nor Join functions because I want to find the formula that consists of two other formulas. I do not try to add G/L account No. so I can't run the results in an NL(Rows) :(
Thanks anyway :) -
Jet Reports Historic Posts Correction!
It may not have worked inside the NL but it worked inside sumif formula!! :D
SUMIF(I:I,NP("Split",K7,"+"),L:L)
However…….it works perfect with one Account Schedule report where there is only one line formula + formula
I run it on another report where this ocurs more than one time and it doesn't work :(
EDIT: False alarm..it only uses the first value. So it doesn't work inside the SUMIF formula :evil: -
Jet Reports Historic Posts Jackies,
Why don't you try something like this in L7:=IF(K7="","",IF(J7="Formula",NL("Sum","G/L Account","Balance at Date","Row No.",NP("Split",K7,"+"),"Date Filter",Options!$D$8,"Account Type","Posting",,,,,,,,,,,,,"company=",Options!$D$9),NL("Sum","G/L Account","Balance at Date","No.",K7,"Date Filter",Options!$D$8,"Account Type","Posting",,,,,,,,,,,,,"company=",Options!$D$9)))
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts fhilton, Row No. is not a valid field from G/L Account table. Row No. is a designated value to a line to use in a formula when you want to add/subtract lines.
Thanks for your help :) -
Jet Reports Historic Posts Okay, I'll try again. How about this?
=IF(K7="","",IF(J7="Formula",NL("Sum","G/L Account","Balance at Date","Date Filter",Options!$D$8,"Account Type","Posting","company=",Options!$D$9,"Link=","Acc. Schedule Line","Totaling","=No.","Row No.",NP("Split",K7,"+")),NL("Sum","G/L Account","Balance at Date","No.",K7,"Date Filter",Options!$D$8,"Account Type","Posting",,,,,,,,,,,,,"company=",Options!$D$9)))
Regards,
Hughes -
Jet Reports Historic Posts I understand the logic of spliting the Row No. by linking the two tables…but NL(Rows) sums G/L accounts. In your example this would work if A1, A2, etc had posting G/L accounts in the formula, where they have other formulas.
I think I need to find a solution outside Jet functions so SUMIF function will sum the amounts from the split (i.e. A1, A2,etc) rows.. :(
EDIT: the ideal scenario would be for this formula to work but it doesn't: SUMIF(I:I,NP("Split",K7,"+"),L:L) -
Jet Reports Historic Posts Hi Jackies,
I don't think it's actually possible to do this with an Excel formula the way you want to, which is why I'm trying to do it with a Jet formula. I think I also forgot a couple filters in my last formula, namely the filters for "Schedule Name" and "Totaling Type" on the Acc. Schedule Line in the linking part of the formula.
Don't worry I already tried it with the rest of the filters :)=IF(K7="","",IF(J7="Formula",NL("Sum","G/L Account","Balance at Date","Date Filter",Options!$D$8,"Account Type","Posting","company=",Options!$D$9,"Link=","Acc. Schedule Line","Totaling","=No.","Totaling Type","Formula","Schedule Name",Options!$D$13,"Row No.",NP("Split",K7,"+")),NL("Sum","G/L Account","Balance at Date","No.",K7,"Date Filter",Options!$D$8,"Account Type","Posting",,,,,,,,,,,,,"company=",Options!$D$9)))
Did you try this formula on your workbook? If this is not working, then maybe I just don't understand exactly what you're trying to do. I think this should be equivalent to doing the SUMIF function you were trying to do. Your SUMIF function is saying that you want to sum the values from the =NL("Sum","G/L Account"…) formulas where the value in column I (the Row No. from the Acc. Schedule Line) is one of the values contained in the current Totaling field (separated by a +). My formula is doing a single NL("Sum") from the G/L Account table where there exists a record on the Acc. Schedule Line table where the Totaling field matches a G/L account number and the Row No. is contained in the current Totaling field. I did forget a couple other filters in the last try for the Acc. Schedule Line table which may make a difference.
I get #VALUE! and the reason why is the one stated on my previous post: split formula can not work in NL(rows, G/L account) because I split formula and not G/L accountsSince I can see that you are trying to sum multiple NL("Sum") formulas which are all going to the same table, we should be able to reproduce this by just doing a single NL("Sum") formula. There should be a way we can filter a single NL("Sum") formula which will give us the equivalent of the sum of other NL("Sum") formulas. Maybe I'm not quite understanding what you want, but I think this should be possible. That SUMIF formula will not work, so we must find a different way to make this work.
The reason is simple: I want a Jet Report that can export any Account Schedule report with an NL(Rows) below to show detailed ledger entries for each account. The only way to do this is create a formula that will calculate posting accounts, formula and sum of formula. I found a solution with the first two but I'm stack with sum of formulas…
Regards,
Hughes
Thanks for your support. Greatly appreciated :) -
Jet Reports Historic Posts Hi!
Okay I think I understand the problem and here is another possible solution. The reason it is giving us #VALUE! is that I was using an NP(Split) inside of Link= which is not allowed. Sorry about that! NP(Split) CAN be used as a filter inside an NL function, just not inside of Link=. So to solve this, I think we can use NP(Join) to join the results of the NP(Split) with a "|" so that it is turned into a regular filter. It would look something like this:
=IF(K7="","",IF(J7="Formula",NL("Sum","G/L Account","Balance at Date","Date Filter",Options!$D$8,"Account Type","Posting","company=",Options!$D$9,"Link=","Acc. Schedule Line","Totaling","=No.","Totaling Type","Formula","Schedule Name",Options!$D$13,"Row No.",NP("Join",NP("Split",K7,"+"),"|")),NL("Sum","G/L Account","Balance at Date","No.",K7,"Date Filter",Options!$D$8,"Account Type","Posting",,,,,,,,,,,,,"company=",Options!$D$9)))
One way this could be a problem would be if the filter gets too long for Nav to handle. As long as it is not too long, it should work fine though. Does this work for you?
Regards,
Hughes -
Jet Reports Historic Posts Still no results…
Moreover, it does not sum up the rows with single formula values.
As I said before, this must be sovled with an excel formula and not JET formula, because the results are already there and I want to sum the columns.
Thanks again for your input. -
Jet Reports Historic Posts Hi Jackies!
Glad you figured it out! Good luck finishing the last bit.
Regards,
Hughes -
Jet Reports Historic Posts A small question :)
I have first tried formula =NL("Columns",NP("Split",K7,"+")) but it didn't work and that is why I then tried =NL("Rows",NP("Split",K7,"+")) which works fine. Anyone know why? -
Jet Reports Historic Posts It's probably because NL(Columns) gets expanded before NL(Rows). Therefore, if you put an NL(Columns) inside an NL(Rows) and it is dependent on the outcome of the row replicator, it will not work. Is that what you are experiencing?
Regards,
Hughes -
Jet Reports Historic Posts Hmmm, maybe you are right. The source comes from an NL(Rows).
Thanks 8-)
Please sign in to leave a comment.