WOW! Thanks Max!

I replaced the sum range with the chunk of formula you recommended and it
appears to work just fine! Yay!

Here is the final formula:

=SUMIF((INDIRECT("'"&$A166&"'!$B$53:$B$311")),$C$3 ,OFFSET(INDIRECT("'"&$A166&"'!$e$53:e$311"),,COLUM NS($A:C)-1))

Can you run through it and explain what Excel is doing to return the sum? I
tried getting Excel to look at the formula, but it just told me it is
volatile.


--
Greg


"Max" wrote:

If you replace your sum range:
INDIRECT("'"&$A166&"'!$e$53:e$311")

with this term:
OFFSET(INDIRECT("'"&$A166&"'!$e$53:e$311"),,COLUMN S($A:A)-1)

it'll give you the flexibility to copy the expression across,
to sum it for cols E, F, G, etc
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"Greg in CO" wrote:
I have two worksheets in the same workbook:

ProjectA
DepartmentA

In DepartmentA, in a cell for January hours, I want Excel to go to ProjectA,
look at an array of cells, and where there is an entry for DepartmentA, I
want Excel to sum the corresponding array of cells in ProjectA, under the
January heading. I have this formula:

=SUMIF((INDIRECT("'"&$A166&"'!$B$53:$B$311")),$C$3 ,(INDIRECT("'"&$A166&"'!$e$53:e$311")))

whe

- A166 is the cell on DepartmentA containing the name for ProjectA
- B53:B311 is the array on ProjectA to look for references to DepartmentA
- C3 is the cell on DepartmentA where is says "DepartmentA"
- E53:E311 is the array to sum, if there is a corresponding reference in
B53:B311

This formula returns the correct response; however, I cannot copy it across
for the rest of the year(s).

Is there a way to use an indirect to reference a worksheet and then
reference the cells on that worksheet so they will change according to their
being absolute or relative cell refs?

Is this a candidate for some SUMPRODUCT magic?

Thanks!


--
Greg