Sum of indirect sheet names with multiple cells (SUM, INDIRECT,SHEETNAME in cell)

Hi guys,

my first run here. Thank you for having me.

Here's what I am trying to solve: I have a file with 6 sheets named like awesome excel forum helpers (e.g. "Peter"). The names and amount of sheets is defined by the column A in sheetnr. 1 (general) looking like this:

SHEET1: general

Name	Sum of Jan	Sum of Feb	Sum of Mar
Peter
Paul
Mary
Chuck
Norris

In the named sheets I have a list of saleries looking like this (showing only Peter and Paul here):

SHEET 2: Peter

Date from	Date until	Sum of Salery	Jan	Feb	Mar
01/01/2013	01/15/2013	50	50	0	0
01/16/2013	01/31/2013	50	50	0	0
02/01/2013	03/31/2013	200	0	100	100

SHEET3: Paul

Date from	Date until	Sum of Salery	Jan	Feb	Mar
01/01/2013	01/31/2013	50	50	0	0
02/01/2013	02/28/2013	100	0	100	0
03/01/2013	03/31/2013	200	0	0	200

I need the sum of the total salery paid to all excel forum helpers in January in the cell B2 in SHEET1 general based on the sheet names A2:A5 and counting all saleries where month in "date untill" is 01.

Concept:
I know I will have to work with
SUM -> To calculate the amount
VLOOKUP -> To find the saleries in January (where month of date till is 01 take column 3)
INDIRECT -> To define the range of sheets in with to vlookup
and
a MATRIX formula -> to use the lookup on the range of B2:B4 AND on all sheets

(

Formula:

Please Login or Register  to view this content.

Now - this is not working of course. I am struggeling at getting this figured out in my head and I thought that "trail&error" won't get me anywhere except maybe to the endless fields of frustration. Any help/suggestions or comments are welcomed. Am I missing something totally obvious? Easier way of doing it? Macrc?

Hope there is someone out there looking for a challenge!!

Cheers and thanks

Rob Steward

--\\ EDIT: 02/12/2013 13:01 Can not have new sheets. Data for each persons has to be seperate.

make a consolidated sheet and add all data on this sheet (can be done with an macro).

after that make a column so you can select the month => =month(a2)

after that use pivot table, to get the desired result.

Hi RobSteward

Perhaps something along the lines of the attached!

I can't have a new sheet. Why not?

Also your solution does not account for the fact, that there might be new excel helpers, which is the reason why I need the variable sheet range.

That won't be a problem.