SUMPRODUCT or SUMIF and INDIRECT, with Relative cell refs

[Greg in CO]

Hi All!

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")))

where:

- 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!

 

[Max]

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

with this term:
OFFSET(INDIRECT("'"&$A166&"'!$e$53:e$311"),,COLUMNS($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]

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"),,COLUMNS($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.

 

[Max]

Welcome. Pl press the "Yes" button below

This term in OFFSET is the column param:
COLUMNS($A:A)-1
It simply produces the series: 0,1,2,3,...
as you copy it across

So in the starting cell, its just basically:
OFFSET(FixedRange,,0)
which returns the FixedRange array itself

In the next cell to the right, it becomes
OFFSET(FixedRange,,1)
which returns an equiv array one col to the right of the FixedRange

And so on, accordingly for
OFFSET(FixedRange,,2)
OFFSET(FixedRange,,3)
etc
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik

 

[Greg in CO]

Thank guys! As usual, amazing help!!!

 

[Greg in CO]

Hi Roger!

I have been playing with the formula you provided below and I have a
question. As I create a named range, it contains the sheet name where the
range resides.

The end reuslt for my efforts is to have "template" sheets for both
departments and projects. This is why I started with the INDIRECT in my
original post, as I want the formulas to be flexible to account for changing
worksheet names. The format of the sheets will be the same, only the names
will change. How would I corporate your named range recommendation into a
formula where both the sheet where the formula resides and the target
sheet(s) names change?

Also, in Max's formula, I can copy it across, but not down.

All help is appreciated!

Greg