Hi,

I am presently using Quik Link for Excel version 6.2A. I have a question concerning the =accpac formula. Here is my question: My chart of account is set up in two segments: 6 digits and 3 letters (dept). The first 4 digits refer to a G/L account while the last 2 digits refer to a subsidiairy or a division of our group. Using Quiklink with this set up to do consolidated financial statements is easy since all of the accounts of the company & the subsidiaries are included in one trial balance. Now, for year-end purposes, I need to prepare individual financial statements for every company of our group. To do so I need to use the =accpac along with the last 2 digits of the first segment. How can I do this?

Hi Marc-Andre,

The solution to this is to use Excel's Concatenate functionality and cell references to get what you want. Quik Link only works with whole account numbers, so you have to 'create' the proper account-department for the =accpac formula.

For example, you have accounts 100044 and 100055 that exists for subsiduaries 44 and 55, respectively. What you have to do is enter into a cell the 2-character code representing the subsiduary (ie 44 or 55) and the cell must be formatted as text. In this example, say you put it into cell A5.

The formula (for link name gldata, field BAL and dept xxx) would be:
=accpac("gldata.BAL","1000"&A5&"-xxx")

When 44 is entered into cell A5, the value for account 100044-xxx is retrieved. If you enter 55 into cell A5, then recalculating the statement (function key F9) will retrieve the value for account 100055-xxx.

Doing it this way means that you can use the same Excel workbook for each subsiduary, assuming that each uses precisely the same 4-character accounts. Simply enter the 2 characters into A5 and recalculate. Note that you can also do the same thing for the department code, where '*' represents a consolidated statement, and the 3 character code is entered for a specific departmental statement. With the same example as above, with the department entered into cell A6, this would be:
=accpac("gldata.BAL","1000"&A5&"-"&A6)
Remember that A6 must also be formatted as text.

Hope this helps out.

[This message has been edited by Softrak Support (edited 11-20-2000).]

Thank you for your quick response. I have tried with succes the Concatenate fonction in Excel as describe in your message. However I wasn't able to use that fonction with a group of account. For example the line Other Revenues in my income statement is made up of accounts 8300 to 8700 and 9800 to 9900. Any suggestion on what I should do? Thank you in advance.

Originally posted by Softrak Support:
Hi Marc-Andre,

The solution to this is to use Excel's Concatenate functionality and cell references to get what you want. Quik Link only works with whole account numbers, so you have to 'create' the proper account-department for the =accpac formula.

For example, you have accounts 100044 and 100055 that exists for subsiduaries 44 and 55, respectively. What you have to do is enter into a cell the 2-character code representing the subsiduary (ie 44 or 55) and the cell must be formatted as text. In this example, say you put it into cell A5.

The formula (for link name gldata, field BAL and dept xxx) would be:
[b]=accpac("gldata.BAL","1000"&A5&"-xxx")

When 44 is entered into cell A5, the value for account 100044-xxx is retrieved. If you enter 55 into cell A5, then recalculating the statement (function key F9) will retrieve the value for account 100055-xxx.

Doing it this way means that you can use the same Excel workbook for each subsiduary, assuming that each uses precisely the same 4-character accounts. Simply enter the 2 characters into A5 and recalculate. Note that you can also do the same thing for the department code, where '*' represents a consolidated statement, and the 3 character code is entered for a specific departmental statement. With the same example as above, with the department entered into cell A6, this would be:
=accpac("gldata.BAL","1000"&A5&"-"&A6)
Remember that A6 must also be formatted as text.

Hope this helps out.

[This message has been edited by Softrak Support (edited 11-20-2000).][/B][/QUOTE]

Hello again, Marc-andre

Really, you have to know Excel well enough to start to 'fine-tune' your Financial report now. We can provide support on Quik Link, but Excel is not our product.

Once the GL data has been successfully 'linked' into your Excel worksheet, any other manipulation that you require to do in order to get the results you want, are going to come from Excel.

Perhaps you could use the Excel SUM function, as well as hard-coding the accounts in to accomodate the account-range requirements?

Regards