Extracting multiple TBs for consolidation purposes

Post by niallc on Wed Jan 28, 2015 8:30 am

I am new to Jet and to nav, so my needs are quite basic for now, im sure that will change!

in any event, we have circa 20 companies in our group, i want to extract the year end trial balances for all of these companies onto one spreadsheet, then i intend to use sumproduct formulae to collate the information on another tab,

For the life of me i cant seem to do it, i can get it to work by using the gl acc nos and an formula to looks ul that gl in each company, but the spreadsheet takes 20 min to update when i open and if i hit the design button it all goes haywire,

i appreciate i am making silly mistakes, but any advice gratefully received.


Post by JET-btr on Wed Jan 28, 2015 10:30 am

I would do something like the attached. Of course you would probably want to set up some sort of date filter but without seeing your report, I'm creating just a basic concept.

Use NL rows to list out the GL Accounts from the GL Account table.

Use NL Columns to look at the Company table to replicate each company in a new column.

Use the GL Balance to look at the account number in that row and the company in that column.

If you are getting error messages, you may want to submit those to Support. Not sure what happens when you say it goes "haywire", if that means it crashes, that it gives an error message or does something else.

If this doesn't work, perhaps you can upload your report for us to look at. There may be a design flaw.


Post by niallc on Wed Jan 28, 2015 10:39 am

thank you for replying

i should have said i have jet express, it says NL columns isnt supported?

i was using the attached only with all gls and all co's across the colums, but i just manually created this, and i assume its sub optimal?


Please sign in to leave a comment.