Consolidate data from multiple sheets
 

Let me apoligize for the vagueness of this questions ahead of time...

I was handed a spreadsheet belonging to my CFO made up of about 40 tabs.
Each tab represents a transaction and is similar although not identical in
layout; The top portion is essentially a form that calculates income. The
bottom portion converts the calculations into Journal entries. The journal
entries may pertain to any of five different entites (BHF, BOF, BOF II, BBB,
BCDO) that we do the accounting for.

The request is: create summary tabs for the five differntent companies that
will show all the relevent journal entries to that entity across all the
transaction tabs. It seems to me I once new trick ways to do this kind of
thing using combinations of ADDRESS, INDEX, and other array formuals but, I'm
forced to admit my skills have slipped.

I've pasted a sample of data below fron one of the transactions tabs: The
range shown is ACNI02!B57:G92

The lines I need to pickup in a summary tab are the line with any of the
following values in Column B {BHF, BOF, BOF II, BBB, BCDO}. Those rows and
the adjacent 5 columns of related data should appear on the correct tab.

BOF JE'S
Ent. TransID Account Amount Memo
BOF 38960ACNI02 Due from BHF_Int and Fees:ACNI02 1,796.11 ACNI02
BOF 38960ACNI02 Facility Fee:ACNI02 ACNI02
BOF 38960ACNI02 Collateral Mngt Fees:ACNI02 (500.00) ACNI02
BOF 38960ACNI02 Interest:Interest Income_Deals:ACNI02 (1,296.11) ACNI02

Due from BHF_Int and Fees:
Facility Fee:
Collateral Mngt Fees:
Interest:Interest Income_Deals:


BOF II JE'S
Entity TransID Account Amount Memo
BOF II 38960ACNI02 Due from BHF_Int and Fees:ACNI02 1,796.11 ACNI02
BOF II 38960ACNI02 Facility Fee:ACNI02 ACNI02
BOF II 38960ACNI02 Collateral Mngt Fees:ACNI02 (500.00) ACNI02
BOF II 38960ACNI02 Interest Income_Deals:ACNI02 (1,296.11) ACNI02

Due from BHF_Int and Fees:
Facility Fee:
Collateral Mngt Fees:
Interest Income_Deals:


BHF JE'S
ENT. TransID Account
Amount Memo
BHF 38960ACNI02 Int and Fees Recv:ACNI01 3,592.22 ACNI02
BHF 38960ACNI02 Due to BOF_Int and Fees:ACNI02 (1,796.11) ACNI02
BHF 38960ACNI02 Due to BOF II_Int and Fees:ACNI02 (1,796.11) ACNI02

Int and Fees Recv:
Due to BOF_Int and Fees:
Due to BOF II_Int and Fees: