Excel functions · Tips and tricks

Will it blend?

squarepeg

If you are using the Multi-Company module with companies in different databases (you will love our Due2Auto product), then you probably have structural differences among your companies. Perhaps the most common difference is where companies have different charts of accounts because they are dissimilar business types. Here’s how to consolidate these in XLstatements.

consol

As usual, I am using the “Solomon” demo data, which has two “application” databases (I don’t like that term, but it’s what Microsoft calls them). Company 0060 (Contoso, Ltd) is in the SLDemoApp60 database and company 0010 (Graphic Design Institute)—which has little data, so I added some—uses the SLDemoApp10 database. Their charts of accounts do not actually differ, but we’ll pretend that they do. In the above example, I created columns for each company (A and B) and used their respective CpnyIDs as headers in row 7. I then created the Acct definitions for the category in each row. Note that I’m pretending that company 0010 has a simpler chart. I also placed the two CpnyIDs at the top of columns D and E, which are for the balances of each company. Note that I would probably hide columns A and B, and row 1, when I save the workbook.

As you can see, the formula in cell D8 is rather long:

=YTDBal(D$1,LedgerID,OFFSET($A8,,MATCH(D$1,$A$7:$B$7,0)-1),SubAcct,FiscYr,Pd-1)+PTDBal(D$1,LedgerID,OFFSET($A8,,MATCH(D$1,$A$7:$B$7,0)-1),SubAcct,FiscYr,Pd)

If you need a refresher on mixing absolute and relative cell references, try and understand this formula. It uses the MATCH function to find the correct column of Acct definitions and then the OFFSET function to refer to the relevant cell. Once this formula is created in that cell, then it can be copied and used for each balance row in every company. I.e., you only need to type the formula in one cell, no matter how many companies you have. If you have multiple databases with different charts of accounts but also multiple companies in one or more of those databases (as is the case with the Solomon demo data, although I’m only including two companies here), then you could use two (hidden) rows at the top of the balance columns: one for the CpnyID and one to identify the Acct definition column to use (e.g., the master company for the database).

Notice the asterisk (*) in cell A3 above? That is the cell named SubAcct that is used for the eponymous argument in the XLstatements balance functions. By using an asterisk wildcard, it combines all subaccounts and is valid even if the subaccount flexkey structures differ among your databases.

If your companies are in separate databases because they have different fiscal years, it’s easy enough to do something similar (and simpler if the COAs are the same). Just add (hidden) rows with a formulas above the balance columns to compute the period and fiscal year for each company, and refer to those cells as the FiscYr and Pd arguments. Examples of such formulas are in the Rolling Rolling Rolling blog post.

One thought on “Will it blend?

  1. Pingback: Compared to What?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s