B
Bam
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($W2:$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($W3:$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($W7:$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.
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($W2:$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($W3:$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($W7:$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.