Sum for multiply user added sheets.

[Guest]

I am working on a resource worksheet with a summary for the resources entered. Users would copy the "project" tab for each project they are forecasting. They could have 1 to many sheets. They would change the tab name to the project ID, then enter resources hours by resource ID

I would then like to have a resource summary tab where the user would enter a resource ID and it would give them the total hours from all sheets for that resource

Example

Sheet

Resource 1: 10
Resource 2: 7
Resource 3: 65

Sheet

Resource 2: 5
Resource 3: 5
Resource 4: 65

Summary Shee

Resource 1: 10
Resource 2: 125
Resource 3: 12
Resource 4: 65

I have tried to build a custom function that would loop through the sheet and sum hours based on the resource ID, but have not had much luck

Hope this makes sence and any help would be grealty appreciated

Kevi

 

[Frank Kabel]

Hi
you may have a look at the SUMIF function.
e.g.
=SUMIF('sheet1'!$A$1:$A$100,"resource_id1",'sheet1'!$B$1:$B$100)
where column A stores the resource ID and column B the values.


--
Regards
Frank Kabel
Frankfurt, Germany

I am working on a resource worksheet with a summary for the resources

entered. Users would copy the "project" tab for each project they are
forecasting. They could have 1 to many sheets. They would change the
tab name to the project ID, then enter resources hours by resource ID.

I would then like to have a resource summary tab where the user would

enter a resource ID and it would give them the total hours from all
sheets for that resource.

Example:

Sheet 1

Resource 1: 100
Resource 2: 75
Resource 3: 65

Sheet 2

Resource 2: 50
Resource 3: 55
Resource 4: 65


Summary Sheet

Resource 1: 100
Resource 2: 125
Resource 3: 120
Resource 4: 65

I have tried to build a custom function that would loop through the

sheet and sum hours based on the resource ID, but have not had much
luck.

 

[Frank Kabel]

Hi
So you want a SUMIF accross multiple sheets. You may try the following

1. Harlan Grove showed a formula approach for a conditional sum accross
multiple worksheets. Have a look at this thread
http://tinyurl.com/2manj

2. You may also try to download the free add-in Morefunc.xll
(http://longre.free.fr/english). The function THREED converts a 3D
array to a 2D array. This may work (the help of this function at least
suggested it). e.g. you may use the following formula

=SUM((THREED('sheet2:sheet50'!A1:A1000)="Resource
1")*(THREED('sheet2:sheet50'!B1:B1000)))
enter this as array formula (CTRL+SHIFT+ENTER). This will sum all
values from column B in which column A contains your criteria


--
Regards
Frank Kabel
Frankfurt, Germany

The number of sheets is unknow. Users will be adding more as they

need them, so the sumif function will not work.

 

[Frank Kabel]

Hi
make your WSLST list a dynamic list. e.g. if you store the sheet names
in column A try the following name defintion
=OFFSET($A$1,0,0,COUNTA($A:$A))

Assumption: no blank rows in between

 

[Guest]

Unfortunatly on the sheet they would be entering the sheet
names, there would be blank rows in between.

 

[Frank Kabel]

Hi
don't you have a chance to restrict this. Otherwise the formulas would
be getting complicated

 

[Guest]

Guess I will have to restrict this.

Thanks for all the help.

Kevin