cell ranges

[nordberg42]

Is it possible to have a range of cells that encompass multiple sheets?

Problem: Sheets 1 through 4 list hours people worked on four different projects, I would like sheet 5 to automatically calculate how many total hours were worked by a partiular person. So if John Smith worked 1 hour on project 1, 3 hours on Proj2, 2 on proj3, 5 on proj4, i would like sheet 5 to say "11" by John Smith's name. I know how to do a conditional sum and it works fine when im only using one sheet but i cant figure out how to have it look in column A of sheet 1 2 3 & 4 and sum the values of column C in sheet 1 2 3 & 4.

Any help would be appreciated

 

[Juan Pablo González]

Array enter this:

=SUM(SUMIF(INDIRECT("Sheet"&ROW(A1:A4)&"!A:A"),A2,INDIRECT("Sheet"&ROW(A1:A4)&"!C:C")))

where the condition is in A2.

 

[nordberg42]

Juan,

Thanks for your response. I am unfamiliar with the indirect function but the way i am currently getting the result i want is with this formula in sheet 5:

=SUM(SUMIF('Project A'!A:A,Consolidated!A1,'Project A'!C:C),SUMIF('Project B'!A:A,Consolidated!A1,'Project B'!C:C),SUMIF(Maintenance!A:A,Consolidated!A1,Maintenance!C:C),
SUMIF(Support!A:A,Consolidated!A1,Support!C:C))

(sheet3 is maintenance and sheet4 is support) So i basically sum the result of the conditional sums on each sheet. this works fine for me now but my worry is when there are 25 projects i'd rather not have a formula six times as long as the one above. If the indirect function will solve my problem but if i have to enter an indirect formula for every sheet in the workbook i guess i'll just keep us my sum formula as i understand it.
Please advise if the indirect will let me quickly enter a formula that encompasses the entire wkbk or if the formula will grow as the # of my wksht's grow. if i can just use one quick indirect formula no matter how many sheets i have then i will study up on it

Im hoping there is a way to define column A of every wksht except the last one as the cell range "name", then i will refernce that name in the range criteria of my formula. Thanks for your time, let me know what you think

 

[Aladin Akyurek]

If you have the free morefunc.xll add-in...

Iinsert 2 blank worksheets, name them First and Last, put all the relevant worksheets between these two, and use...

=SUMPRODUCT((THREED(First:Last!$A$1:$A$200)=Consolidated!A1)+0,(THREED(First:Last!$B$1:$B$200)))

 

[Juan Pablo González]

The following works, but I'm still trying to create a named range that refers to all but the last sheet, so, follow me:

1. Go to Insert | Name, Define

and define

Sheets

which refers to

=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")

2. We're going to SUMIF A:A of every sheet, with the criteria that is in A2, and sum columns C:C

so, the formula in B1 is

=SUM(SUMIF(INDIRECT("'"&Sheets&"'!A:A"),A2,INDIRECT("'"&Sheets&"'!B:B")))

again, array entered.

Note that we can't have both the criteria and the formula in cells A2 and C2 respectivly, because that creates a circular reference. So, its only possible to have one at a time... If you have a value in C2 in the last sheet, it will be added to the SUMIF above, so, don't put a value there, or move the criteria to B2 for example.