NL(Rows, with empty fields

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.



Please sign in to leave a comment.