NL("Rows";"G/L Account") only for used accounts?

Hi Community,

I have an … interesting … problem at hand. Sorry for the Wall of Text in advance, it is complicated to explain without example, and my file is rather large to share.

I am currently working on a financial report, where we compare a period, to full year & last year. Say the datefilter is 01-04-2016..30-06-2016, we compare Q2, compared to YTD 2016, and both colums last year as well. In essence, 4 different periods. Though the ACTUAL reporting period is Q2 2016.

Q2 2016 Q2 2015 YTD 2016 YTD 2015
Revenue Local 100 0 150 0
Revenue Abroard 0 100 0 -100

To retreive this information, we have a sheet with the Specification for both result and balance. Now, because we recently expanded our CoA quite a lot to prepare for future reporting demands (IFRS amongst others), there are a lot of accounts without movement, which I would prefer filtered away in the specification, as that has become very long.

Currently I simply use : =NL("Rows";"15 G/L Account";;"No.";"10000..19000"). Which will list all our Revenue accounts. In this interval we probably have 25 accounts we haven't used yet (R&D Company).

Ideally I would like to add a date and balance filter as such : =NL("Rows";"15 G/L Account";;"No.";"10000..19000";"Datefilter";"01-04-2016..30-06-2016";"Balance";"<>0"). So accounts without balance in the period won't be included in my Repeater.

Here is my problem though. Even if there are no movements in Q2 2016, there might be movement during Q2 2015, so we have to show the difference in Revenue and explain. If I take my example again:

Q2 2016 Q2 2015 YTD 2016 YTD 2015
Revenue Local 250 0 150 0
Revenue from Resale 0 100 0 -100
Revenue Others -150 0 150 0

If i filter <>0 in the period, Revenue Abroar will be excluded. But the movements will affect Cash-Flow differently, as they periods are different. Likewise if the <>0 is set to full reporting period, Revenue Abroard AND Revenue Others will be excluded. In this example I am using a "Resale" that needs to be reconciled - but this problem is especially problematic when we reconcile Loans and Investments, which we do a lot.

Ideally, I would like a filter that allows me to do something along the lines of : IF(NP("First";"G/L Entries";"insert account number";"Datefilter";"01-01-2015..30-06-2016";"Balance";"<>0");true = repeater; false = dont).

But you can't do Repeaters within Excel functions unfortunately. My second idea was to somehow use =NL("Rows";"15 G/L Account";;"No.";"10000..19000";"NL("Filter";…..) where the filter is a true/false statement to check whether the account has entries or not. But I'm not sure this is possible.

Does anyone have any bright idea to save me literally 100's of useless specification lines?


/Kemad