ExcelBanter - formula partial change

On Mar 30, 11:49 am, "Jimbo" wrote:
Is there a way to carry a formula down cells and have only part of the
formula change. Need to reference the cell next to it, but not the
lookup formula. need to keep column C from changing, but need to have
B Cell Reference change, as I drop formula down.

I have 60-80 cells, and this get tedious.

=SUMPRODUCT(--EXACT('3-2007'!C1:C551,B54))+SUMPRODUCT(--EXACT('2-2007'!
C1:C551,B54))+SUMPRODUCT(--EXACT('1-2007'!C1:C551,B54))+SUMPRODUCT(--
EXACT('12-2006'!C1:C551,B54))

Thanks in advance.

=SUMPRODUCT(--EXACT('3-2007'!$C$1:$C$551,B54))+SUMPRODUCT(--
EXACT('2-2007'!$C$1:$C$551,B54))+SUMPRODUCT(--EXACT('1-2007'!$C$1:$C
$551,B54))*SUMPRODUCT(--EXACT('12-2006'!$C$1:$C$551,B54))

Anywhere you don't want the formula changing, put a $ sign. eg - you
want C1:C551 to remain static, put the $ in front of both the column
and row reference $C$1:$C$551. This copied down will remain exactly
like it is.

But if you want only the column reference to remain static, just put
the dollar infront of that part $C1:$C551. This when copied down will
turn to $C2:$C552

No $'s in the B references, as you mentioned you didn't want it to be
static.