Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically change ranges when copying formula
I have the following formulas in my worksheet
Row 2 =SUMPRODUCT(($N2:$N9<$H2)*($O2:$O9<$G$1)*($N2:$N9 =$L2)) Row 10 =SUMPRODUCT(($N10:$N12<$H10)*($O10:$O12<$G$1)*($N1 0:$N12=$L10)) Row 13 =SUMPRODUCT(($N13:$N20<$H13)*($O13:$O20<$G$1)*($N1 3:$N20=$L13)) As you can see there are different ranges in the formulas. What I am doing is copying the formulas into the relevant cells and manually changing the ranges. Having over 3000 to do will take a lot of time! Is there and easy way of copying the formula down the sheet to pick up the ranges. Each new range row is highlighted in TAN so I dont know if this could be used to find the new range. Any help would be appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically change ranges when copying formula
perhap, try using Name range, such as
naming col N's data to its header and use the name in the formula. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Mally" wrote: I have the following formulas in my worksheet Row 2 =SUMPRODUCT(($N2:$N9<$H2)*($O2:$O9<$G$1)*($N2:$N9 =$L2)) Row 10 =SUMPRODUCT(($N10:$N12<$H10)*($O10:$O12<$G$1)*($N1 0:$N12=$L10)) Row 13 =SUMPRODUCT(($N13:$N20<$H13)*($O13:$O20<$G$1)*($N1 3:$N20=$L13)) As you can see there are different ranges in the formulas. What I am doing is copying the formulas into the relevant cells and manually changing the ranges. Having over 3000 to do will take a lot of time! Is there and easy way of copying the formula down the sheet to pick up the ranges. Each new range row is highlighted in TAN so I dont know if this could be used to find the new range. Any help would be appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically change ranges when copying formula
Do NOT use $ in front of the Col letter and/or Row number if you want it to
CHANGE while copying. $ indicates absolute references which do not change... like in $G$1 below Try in row 2 =SUMPRODUCT((N2:N9<H2)*(O2:O9<$G$1)*(N2:N9=L2)) This will change to =SUMPRODUCT((N10:N17<H10)*(O10:O17<$G$1)*(N10:N17 =L10)) when copied to row 10 and to =SUMPRODUCT((N13:N20<H13)*(O13:O20<$G$1)*(N13:N20 =L13)) in row 13 "Mally" wrote: I have the following formulas in my worksheet Row 2 =SUMPRODUCT(($N2:$N9<$H2)*($O2:$O9<$G$1)*($N2:$N9 =$L2)) Row 10 =SUMPRODUCT(($N10:$N12<$H10)*($O10:$O12<$G$1)*($N1 0:$N12=$L10)) Row 13 =SUMPRODUCT(($N13:$N20<$H13)*($O13:$O20<$G$1)*($N1 3:$N20=$L13)) As you can see there are different ranges in the formulas. What I am doing is copying the formulas into the relevant cells and manually changing the ranges. Having over 3000 to do will take a lot of time! Is there and easy way of copying the formula down the sheet to pick up the ranges. Each new range row is highlighted in TAN so I dont know if this could be used to find the new range. Any help would be appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically change ranges when copying formula
Hi Francis
Thanks for your reply. I'm having a look at the name range to see if it will work. "Francis" wrote: perhap, try using Name range, such as naming col N's data to its header and use the name in the formula. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Mally" wrote: I have the following formulas in my worksheet Row 2 =SUMPRODUCT(($N2:$N9<$H2)*($O2:$O9<$G$1)*($N2:$N9 =$L2)) Row 10 =SUMPRODUCT(($N10:$N12<$H10)*($O10:$O12<$G$1)*($N1 0:$N12=$L10)) Row 13 =SUMPRODUCT(($N13:$N20<$H13)*($O13:$O20<$G$1)*($N1 3:$N20=$L13)) As you can see there are different ranges in the formulas. What I am doing is copying the formulas into the relevant cells and manually changing the ranges. Having over 3000 to do will take a lot of time! Is there and easy way of copying the formula down the sheet to pick up the ranges. Each new range row is highlighted in TAN so I dont know if this could be used to find the new range. Any help would be appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically change ranges when copying formula
Hi Sheeloo
Thank you for your reply. The ranges specified in the formulas are the ranges that i need so by taking out the $ and copying the formulas will pick up the incorrect ranages. Thank you again. "Sheeloo" wrote: Do NOT use $ in front of the Col letter and/or Row number if you want it to CHANGE while copying. $ indicates absolute references which do not change... like in $G$1 below Try in row 2 =SUMPRODUCT((N2:N9<H2)*(O2:O9<$G$1)*(N2:N9=L2)) This will change to =SUMPRODUCT((N10:N17<H10)*(O10:O17<$G$1)*(N10:N17 =L10)) when copied to row 10 and to =SUMPRODUCT((N13:N20<H13)*(O13:O20<$G$1)*(N13:N20 =L13)) in row 13 "Mally" wrote: I have the following formulas in my worksheet Row 2 =SUMPRODUCT(($N2:$N9<$H2)*($O2:$O9<$G$1)*($N2:$N9 =$L2)) Row 10 =SUMPRODUCT(($N10:$N12<$H10)*($O10:$O12<$G$1)*($N1 0:$N12=$L10)) Row 13 =SUMPRODUCT(($N13:$N20<$H13)*($O13:$O20<$G$1)*($N1 3:$N20=$L13)) As you can see there are different ranges in the formulas. What I am doing is copying the formulas into the relevant cells and manually changing the ranges. Having over 3000 to do will take a lot of time! Is there and easy way of copying the formula down the sheet to pick up the ranges. Each new range row is highlighted in TAN so I dont know if this could be used to find the new range. Any help would be appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically change ranges when copying formula
Mally,
Sorry. I did not notice that the second formula was different... Where are you copying these to? I mean which rows will have the formula in row 2, which rows will have the formula in row 10, row13? If the formulas follow the same pattern then enter thee formulas below in the row indicated, select row 1-13 and drag down.... row 2 =SUMPRODUCT((N2:N9<H2)*(O2:O9<$G$1)*(N2:N9=L2)) row 10 =SUMPRODUCT((N10:N12<H10)*(O10:O12<$G$1)*(N10:N17 =L10)) row 13 =SUMPRODUCT((N13:N20<H13)*(O13:O20<$G$1)*(N13:N20 =L13)) "Mally" wrote: Hi Sheeloo Thank you for your reply. The ranges specified in the formulas are the ranges that i need so by taking out the $ and copying the formulas will pick up the incorrect ranages. Thank you again. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically change ranges when copying formula
Hi again Sheeloo
Thank you for your reply. My problem is that there is no pattern to my formulas. The ranges are different in every formula i.e. each individual row formula looks at a different number of cells in a range. So thats why i can't copy it down a column. "Sheeloo" wrote: Mally, Sorry. I did not notice that the second formula was different... Where are you copying these to? I mean which rows will have the formula in row 2, which rows will have the formula in row 10, row13? If the formulas follow the same pattern then enter thee formulas below in the row indicated, select row 1-13 and drag down.... row 2 =SUMPRODUCT((N2:N9<H2)*(O2:O9<$G$1)*(N2:N9=L2)) row 10 =SUMPRODUCT((N10:N12<H10)*(O10:O12<$G$1)*(N10:N17 =L10)) row 13 =SUMPRODUCT((N13:N20<H13)*(O13:O20<$G$1)*(N13:N20 =L13)) "Mally" wrote: Hi Sheeloo Thank you for your reply. The ranges specified in the formulas are the ranges that i need so by taking out the $ and copying the formulas will pick up the incorrect ranages. Thank you again. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically change ranges when copying formula
If there is no pattern then you will have to do it manually...
"Mally" wrote: Hi again Sheeloo Thank you for your reply. My problem is that there is no pattern to my formulas. The ranges are different in every formula i.e. each individual row formula looks at a different number of cells in a range. So thats why i can't copy it down a column. "Sheeloo" wrote: Mally, Sorry. I did not notice that the second formula was different... Where are you copying these to? I mean which rows will have the formula in row 2, which rows will have the formula in row 10, row13? If the formulas follow the same pattern then enter thee formulas below in the row indicated, select row 1-13 and drag down.... row 2 =SUMPRODUCT((N2:N9<H2)*(O2:O9<$G$1)*(N2:N9=L2)) row 10 =SUMPRODUCT((N10:N12<H10)*(O10:O12<$G$1)*(N10:N17 =L10)) row 13 =SUMPRODUCT((N13:N20<H13)*(O13:O20<$G$1)*(N13:N20 =L13)) "Mally" wrote: Hi Sheeloo Thank you for your reply. The ranges specified in the formulas are the ranges that i need so by taking out the $ and copying the formulas will pick up the incorrect ranages. Thank you again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change row and column when copying formula | Excel Discussion (Misc queries) | |||
Why Excel does automatically change the formula? | Excel Worksheet Functions | |||
Copying formula with cell reference decreasing automatically | Excel Discussion (Misc queries) | |||
formula help to change row colour automatically by some conditions | Excel Worksheet Functions | |||
Is there a way to automatically change the location of a formula? | Excel Worksheet Functions |