formula partial change

J

Jimbo

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.
 
O

okrob

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top