INDEX - MATCH - OFFSET By Reference
 

Hi All,

I'm having troubles writing this array based on criteria I hold in Column B.

{=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($W 2:$AW2,,,,COLUMN($W2:$AW2)-MIN(COLUMN($W2:$AW2))+1))$R2,0))}

Example:
Row 1 is The Header Row.
Cell B2 = FRED
Cell B3 = GREG
Cell B4 = GREG
Cell B5 = GREG
Cell B6 = GREG
Cell B7 = HAZY
Cell B8 = HAZY

If I use the formula above, the calcualtion for "FRED" is correct because
I'm only looking at Row 2 in the formula.

For "GREG" though, i need the formula to change to this in EACH of the GREG
rows.

{=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($W 3:$AW6,,,,COLUMN($W3:$AW6)-MIN(COLUMN($W3:$AW6))+1))SUM(R3:R6),0))}

For "HAZY"
{=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($W 7:$AW8,,,,COLUMN($W7:$AW8)-MIN(COLUMN($W7:$AW8))+1))SUM(R7:R8),0))}

etc...

Column B is always sorted alphabetical if that makes it easier.

Any help would be much appreciated??

Cheers,

Bam.

INDEX - MATCH - OFFSET By Reference
 

Is this what you mean? Since the data is sorted by Column B, in
ascending order, assuming that B15 contains Fred, B16 contains Greg, and
B17 contains Hazy, first define the following...

Select C15

Insert Name Define

Name: Col_R

Refers to:

=INDEX($R$2:$R$8,MATCH($B15,$B$2:$B$8,0)):INDEX($R $2:$R$8,MATCH($B15,$B$2
:$B$8,1))

Click Add

Name: Data

Refers to:

=INDEX($W$2:$W$8,MATCH($B15,$B$2:$B$8,0)):INDEX($A W$2:$AW$8,MATCH($B15,$B
$2:$B$8,1))

Click Ok

Then try...

C15, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET(Dat a,,,,COLUMN(Data)-MIN(C
OLUMN(Data))+1))SUM(Col_R),0))

or

=INDEX($W$1:$AW$1,MATCH(TRUE,MMULT(TRANSPOSE(ROW(D ata)^0),MMULT(IF(Data<
"",Data,0),(COLUMN(Data)=TRANSPOSE(COLUMN(Data))) +0))SUM(Col_R),0))

Adjust the ranges accordingly. Note that the second formula avoids the
volatile function OFFSET. However, MMULT will return #VALUE! if the
output of cells exceeds 5,460 for PC or 4,095 for the Mac. If this is
the case, download and install the free add-in, Morefunc.xll, and use
MMULT.EXT instead. Unfortunately, the add-in is not compatible with the
Macintosh computer.

--
Domenic
http://www.xl-central.com

In article ,
Bam wrote:

Hi All,

I'm having troubles writing this array based on criteria I hold in Column B.

{=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($W 2:$AW2,,,,COLUMN($W2:$AW2)-M
IN(COLUMN($W2:$AW2))+1))$R2,0))}

Example:
Row 1 is The Header Row.
Cell B2 = FRED
Cell B3 = GREG
Cell B4 = GREG
Cell B5 = GREG
Cell B6 = GREG
Cell B7 = HAZY
Cell B8 = HAZY

If I use the formula above, the calcualtion for "FRED" is correct because
I'm only looking at Row 2 in the formula.

For "GREG" though, i need the formula to change to this in EACH of the GREG
rows.

{=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($W 3:$AW6,,,,COLUMN($W3:$AW6)-M
IN(COLUMN($W3:$AW6))+1))SUM(R3:R6),0))}

For "HAZY"
{=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($W 7:$AW8,,,,COLUMN($W7:$AW8)-M
IN(COLUMN($W7:$AW8))+1))SUM(R7:R8),0))}

etc...

Column B is always sorted alphabetical if that makes it easier.

Any help would be much appreciated??

Cheers,

Bam.

INDEX - MATCH - OFFSET By Reference
 

Thanks Domenic.

Once I started understanding what you were doing, i've manged to get it to
do what i "think" i need.

I hadn't defined names previously, so you've just enlightened me! I can use
it on a lot of my spreadsheets, so much appreciated.

Cheers,

Bam.


"Domenic" wrote:

Is this what you mean? Since the data is sorted by Column B, in
ascending order, assuming that B15 contains Fred, B16 contains Greg, and
B17 contains Hazy, first define the following...

Select C15

Insert Name Define

Name: Col_R

Refers to:

=INDEX($R$2:$R$8,MATCH($B15,$B$2:$B$8,0)):INDEX($R $2:$R$8,MATCH($B15,$B$2
:$B$8,1))

Click Add

Name: Data

Refers to:

=INDEX($W$2:$W$8,MATCH($B15,$B$2:$B$8,0)):INDEX($A W$2:$AW$8,MATCH($B15,$B
$2:$B$8,1))

Click Ok

Then try...

C15, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET(Dat a,,,,COLUMN(Data)-MIN(C
OLUMN(Data))+1))SUM(Col_R),0))

or

=INDEX($W$1:$AW$1,MATCH(TRUE,MMULT(TRANSPOSE(ROW(D ata)^0),MMULT(IF(Data<
"",Data,0),(COLUMN(Data)=TRANSPOSE(COLUMN(Data))) +0))SUM(Col_R),0))

Adjust the ranges accordingly. Note that the second formula avoids the
volatile function OFFSET. However, MMULT will return #VALUE! if the
output of cells exceeds 5,460 for PC or 4,095 for the Mac. If this is
the case, download and install the free add-in, Morefunc.xll, and use
MMULT.EXT instead. Unfortunately, the add-in is not compatible with the
Macintosh computer.

--
Domenic
http://www.xl-central.com

In article ,
Bam wrote:

Hi All,

I'm having troubles writing this array based on criteria I hold in Column B.

{=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($W 2:$AW2,,,,COLUMN($W2:$AW2)-M
IN(COLUMN($W2:$AW2))+1))$R2,0))}

Example:
Row 1 is The Header Row.
Cell B2 = FRED
Cell B3 = GREG
Cell B4 = GREG
Cell B5 = GREG
Cell B6 = GREG
Cell B7 = HAZY
Cell B8 = HAZY

If I use the formula above, the calcualtion for "FRED" is correct because
I'm only looking at Row 2 in the formula.

For "GREG" though, i need the formula to change to this in EACH of the GREG
rows.

{=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($W 3:$AW6,,,,COLUMN($W3:$AW6)-M
IN(COLUMN($W3:$AW6))+1))SUM(R3:R6),0))}

For "HAZY"
{=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($W 7:$AW8,,,,COLUMN($W7:$AW8)-M
IN(COLUMN($W7:$AW8))+1))SUM(R7:R8),0))}

etc...

Column B is always sorted alphabetical if that makes it easier.

Any help would be much appreciated??

Cheers,

Bam.