Blogger
Share this content
0
1
3841

Intercompany balances

I need to create a matrix to check the intercompany balances for a group of 40-50 companies. I'm currently using Excel 2010 and have been told that pivot tables or conditional formatting may be of use.

Sadly I'm no expert on Excel (especially 2010), so any detailed help would be greatly appreacated.

Thanks in advance!

Replies

Please login or register to join the discussion.

avatar
By BillC
13th Feb 2012 04:08

This has been answered before

There are a number of solutions to this and one of them appears here:

http://www.accountingweb.co.uk/anyanswers/intercompany-balances-matrix-formatting-show-mismatches 

If you follow the logic in the post, you need to ensure that the company names are exactly the same horizontally and vertically.  If not you will have red cells even if the balances agree.

Helpful tip.

Create the matrix small scale at first with a blank column and a blank row between the last company name and the total for the row or column  Make sure that the blank column and row are included in the conditional formatting formula.

Once you have proved that the matrix works small scale you can insert columns and rows by highlighting the blank columns and rows, and dragging as far as you need to.  The conditional formatting will automatically be included in the new, larger matrix.

Hope this helps.

Thanks (0)