cosolidating report

[bball95]

How can i retrieve data from multiple work sheets?

example, for payroll retreive payroll numbers for current and prior years, note that the payroll expense line item is not always on the same row.

i attached a spreadsheet that shows the report format.

[DGagnon]

you could use a series of VLOOKUPs like this:

=VLOOKUP(A9,'Company 1'!A:B,2,0)+VLOOKUP(A9,'Company 2'!A:B,2,0)+VLOOKUP(A9,'Company 3'!A:B,2,0)

and this

=VLOOKUP(A9,'Company 1'!A:C,3,0)+VLOOKUP(A9,'Company 2'!A:C,3,0)+VLOOKUP(A9,'Company 3'!A:C,3,0)

[Haseeb Avarakkan]

Or,

enter all sheet names (except summary) in a range. eg: J2:J4, then use SUMIF

B9,

Please Login or Register  to view this content.

C9,

Please Login or Register  to view this content.

Then select both & drag down.

[bball95]

Cool both work. Now i will use the formulas on my complex financial statement. I just need to tweak my data extractions then the formulas will work. i have another formula question. will post in a bit just going to add couple sheet to show you.

[bball95]

Attached is a revised report that requires the following formula:

Department 1 is from Company 1. Now Department one allocates 40% of expenses to Company 2 and 40% expenses to Company 3. At the same time 80% of expenses from Department 1 are subtracted from Company 1.

Is there or are there formulas that can assist me with this?