Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula partial change
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to BOLD partial number | New Users to Excel | |||
If Formula When Contains Partial Word | Excel Worksheet Functions | |||
Partial Text Color Change in Cell | Excel Discussion (Misc queries) | |||
Formula to calculate partial payments | Excel Worksheet Functions | |||
format partial formula | Excel Programming |