Making a table for multiple sheets Excel 2007

[REVILO]

I have a workbook with 7 or more sheets of data and a sheet named table.
I have an IF statement in column K which looks at the date of the activity,
(H2<0,"Over Due",0). In column L is the activity, Title, Design, Purchase,
install, etc.
On the sheet Table I have a table with activity in cloumn A2:A100 and sheet
names in row B2 : B8, this is my table. I need to look in column L (All
Sheets we data) if Over Due is found then my table will display the results
in the relevent section of the table. Please can you help?

General Section1 Section2
Title 3 2 5
Design 1 3 4
Purchase 2 2 1

I would appreciate a formula and VBA Code. Thanks

 

[Joel]

I don't think you need a macro. You can use Indirect to get the sheet
addresses and sumproduct to look at two columns on each sheet. I think you
meant in your instructions that the sheet names are in B1:H1 (not B2:B8).

Put this formula in cell b2 and copy to all locations in your table

=SUMPRODUCT(--(INDIRECT(B$1&"!K1:K1000")="Over
Due"),--(INDIRECT(B$1&"!L1:L1000")=$A2))

B1 will be the sheet name
K1:K1000 and L1:L1000 is the data cell on each data sheet
A2 is the activity.

 

[REVILO]

Yes you are correct row 1 has sheet names.
The formula works a treat.