| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DO NOT WANT ROW NUMBER IN FORMULA TO CHANGE
Have following formula on cell J1
(=SUMPRODUCT(--($C$3:$C$157DATE(2008,9,30)),--($C$3:$C$157<DATE(2008,11,1)),$G$3:$G$157) If I insert a line on row 3, formula changes to $C$4 and $G$4, I want it to stay as formula is written. Is it possible? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DO NOT WANT ROW NUMBER IN FORMULA TO CHANGE
Yes.
Try =SUMPRODUCT(--(INDIRECT("$C$3:$C$157")DATE(2008,9,30)),--(INDIRECT("$C$3:$C$157")<DATE(2008,11,1)),INDIRECT ("$G$3:$G$157")) "Angie G." wrote: Have following formula on cell J1 (=SUMPRODUCT(--($C$3:$C$157DATE(2008,9,30)),--($C$3:$C$157<DATE(2008,11,1)),$G$3:$G$157) If I insert a line on row 3, formula changes to $C$4 and $G$4, I want it to stay as formula is written. Is it possible? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DO NOT WANT ROW NUMBER IN FORMULA TO CHANGE
Thanks, it worked.
"Sheeloo" wrote: Yes. Try =SUMPRODUCT(--(INDIRECT("$C$3:$C$157")DATE(2008,9,30)),--(INDIRECT("$C$3:$C$157")<DATE(2008,11,1)),INDIRECT ("$G$3:$G$157")) "Angie G." wrote: Have following formula on cell J1 (=SUMPRODUCT(--($C$3:$C$157DATE(2008,9,30)),--($C$3:$C$157<DATE(2008,11,1)),$G$3:$G$157) If I insert a line on row 3, formula changes to $C$4 and $G$4, I want it to stay as formula is written. Is it possible? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DO NOT WANT ROW NUMBER IN FORMULA TO CHANGE
INDIRECT evaluates the references as TEXT strings and as such, will never
change when copying the formula or inserting/deleting rows/columns so you can save a few keystrokes by eliminating the $ signs: =SUMPRODUCT(--(INDIRECT("C3:C157")DATE(2008,9,30)),--(INDIRECT("C3:C157")<DATE(2008,11,1)),INDIRECT("G3 :G157")) -- Biff Microsoft Excel MVP "Angie G." wrote in message ... Thanks, it worked. "Sheeloo" wrote: Yes. Try =SUMPRODUCT(--(INDIRECT("$C$3:$C$157")DATE(2008,9,30)),--(INDIRECT("$C$3:$C$157")<DATE(2008,11,1)),INDIRECT ("$G$3:$G$157")) "Angie G." wrote: Have following formula on cell J1 (=SUMPRODUCT(--($C$3:$C$157DATE(2008,9,30)),--($C$3:$C$157<DATE(2008,11,1)),$G$3:$G$157) If I insert a line on row 3, formula changes to $C$4 and $G$4, I want it to stay as formula is written. Is it possible? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to automatically change one number to another number | Excel Discussion (Misc queries) | |||
| Formula to change scientific number to regular number or text | Excel Discussion (Misc queries) | |||
| want to copy formula, only change one number in formula | Excel Worksheet Functions | |||
| Is there a formula to change number value to the written value? | Excel Discussion (Misc queries) | |||
| Is it possible to change the "result of a formula" to a "number? | Excel Discussion (Misc queries) | |||
Hybrid Mode
