SUMPRODUCT or SUMIF and INDIRECT, with Relative cell refs
Greg in CO
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!
--
Greg
Max
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
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.
--
Greg
Max
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)
Greg in CO
--
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
> Insert>Name>Define>Name 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" <Greg...@discussions.microsoft.com> wrote in message
> news:26CE317A-B2C4-478D...@microsoft.com...
Greg in CO
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
> Insert>Name>Define>Name 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" <Greg...@discussions.microsoft.com> wrote in message
> news:26CE317A-B2C4-478D...@microsoft.com...