SUMPRODUCT or SUMIF and INDIRECT, with Relative cell refs
 

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

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

SUMPRODUCT or SUMIF and INDIRECT, with Relative cell refs
 

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

SUMPRODUCT or SUMIF and INDIRECT, with Relative cell refs
 

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

SUMPRODUCT or SUMIF and INDIRECT, with Relative cell refs
 

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" wrote:
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.

SUMPRODUCT or SUMIF and INDIRECT, with Relative cell refs
 

Thank guys! As usual, amazing help!!! :)
--
Greg


"Roger Govier" wrote:

Hi Greg

If you use some named ranges, you could simplify the formula, and remove the
Volatile Offset function.

Name your range B53:B311 in the relevant sheet as ColB
InsertNameDefineName ColB Refers to B53:B311

Name the range E53:P311 as myData

then use
=SUMIF(ColB,$C$3,INDEX(myData,,COLUMN(A1)))
and copy across as required.
--
Regards
Roger Govier

"Greg in CO" wrote in message
...
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

SUMPRODUCT or SUMIF and INDIRECT, with Relative cell refs
 

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


"Roger Govier" wrote:

Hi Greg

If you use some named ranges, you could simplify the formula, and remove the
Volatile Offset function.

Name your range B53:B311 in the relevant sheet as ColB
InsertNameDefineName ColB Refers to B53:B311

Name the range E53:P311 as myData

then use
=SUMIF(ColB,$C$3,INDEX(myData,,COLUMN(A1)))
and copy across as required.
--
Regards
Roger Govier

"Greg in CO" wrote in message
...
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