We have been using Dynalynk for Excel v.5 to extract the GL accounts from ACCPAC Plus for the presentation of financial statements until we have upgraded our Excel to v.7 recently. As Dynalynk does not support Excel v.7, our local dealer recommended QuikLink as the simplest solution.

By following the instructions given by the dealer, we managed to convert our existing financial statements from the Dynalynk format into QuikLink format without changing any Excel cell formulae. The reports seem fined for companies without GL department. However, for companies with departments, cell formulae which point to a single account number is correct but not for the cell formulae which refers to a range of account numbers. For the latter, the answer shows a cumulative total of all the departments instead of the particular department.

A sample of the cell formula pointing to a range of GL expenses accounts for a particular department is:
=ACCPAC("gldata.M",$A69&"-"&$D$8)
where A69 point to the account numbers (610000-619999) and D8 refers to the department code (OL1).

The dealer informed that the problem lies with QuikLink for Excel v.7 which does not recognize department codes which are alphanumeric. We then tested a sample data with numeric department code but the answer is still wrong as before.

Please advise.

Thank you

Hi ODCSPL,

If you wish to create a cell that sums a range of account for a specific department, we've found that a certain syntax is required for the formula. For the range you specified, the syntax would be:

=ACCPAC("gldata.M","610000:619999-OL1: OL1")

The difference is that an extra ': OL1' had to be added to the end of the account key string. Otherwise, the formula returned the sum of all accounts in the range of 610000 to 619999-OL1 inclusive, for all departments and not limited to OL1.

Because you are using cell references to pick up the account and department, there are two ways you can address this:

1) If all your =ACCPAC formulas are based on account ranges (and not a single account), then the easiest change to make is simply modify the cell that contains the departments. Instead of OL1, use OL1: OL1.
2) If you have =ACCPAC formulas based on a single account, the above change will cause problems to these formulas. In this case, you won't be able to make a 'global' change like this. Instead, you have to edit the formula to add the department cell reference again. Thus the formula you posted would be:

=ACCPAC("gldata.M",$A69&"-"&$D$8&":"&$D$8)

If I've posted this correctly, the portion in bold must be added, which is just a repeat of the department cell reference.

Regards,