Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy down a range of formulae
Is there any procedure to copy down a range of formulae (say A6:J6) based on
a cell value (b2)? I have formulae on a6:j6. I would like to copy down them upto the value that is entered in b2. Thank you all Sridhar |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy down a range of formulae
without VB, you could number a predetermined set of rows. Then put your
formula into an 'if' statement that says if(currentrow<b2,formula,"")to keep the cell blank. if you had the formulas already copied and in place, you would see only blanks for the rows past the number you chose in b2. "yshridhar" wrote: Is there any procedure to copy down a range of formulae (say A6:J6) based on a cell value (b2)? I have formulae on a6:j6. I would like to copy down them upto the value that is entered in b2. Thank you all Sridhar |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy down a range of formulae
If you mean for example with 6 in B1, for the 5 rows below A6:J6 to show a
result of a formula then enter in A7:J7 a formula like: =IF(ROW()-6<$B$1,<Your formula,"") and copy down as far as you will ever need. The formulas will only show down as far ar the number of rows sprecified in B1. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "yshridhar" wrote in message ... Is there any procedure to copy down a range of formulae (say A6:J6) based on a cell value (b2)? I have formulae on a6:j6. I would like to copy down them upto the value that is entered in b2. Thank you all Sridhar |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy down a range of formulae
Thank you Sandy. The formula works but not my problem. I have to copy down
the formulae upto J1000. As these are array formulae it is taking some time to finish the calculation. What i want is a procedure that removes the formulae first from A7:J (previous copy range), and copies the formulae of A6:j6 down the value in B1. Any suggestions pls. Thank you once again Sandy for your formula. Regards Sridhar "Sandy Mann" wrote: If you mean for example with 6 in B1, for the 5 rows below A6:J6 to show a result of a formula then enter in A7:J7 a formula like: =IF(ROW()-6<$B$1,<Your formula,"") and copy down as far as you will ever need. The formulas will only show down as far ar the number of rows sprecified in B1. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "yshridhar" wrote in message ... Is there any procedure to copy down a range of formulae (say A6:J6) based on a cell value (b2)? I have formulae on a6:j6. I would like to copy down them upto the value that is entered in b2. Thank you all Sridhar |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy down a range of formulae
Thank you Sandy for your explanation. After your suggestion i changed my
data and reduced the formulae. Thanks alot Sridhar "Sandy Mann" wrote: Copying formulas down by VBA will be slow and then the array formula will still have to calculate so what you are asking for will be slower, not faster, except for small numbers of rows. As an experiment I created a formula in A1: =IF($K$1=1,"",SUMPRODUCT((M1:Z10000)*(N1:N10000))) and copied it across to J1 and then down to Row 1037 - some 10,370 formulas Deleting the 1 from K1 caused the SUMPRODUCT() part to calculate and it took about 10 minutes to calculate. Entering the 1 back into K1 whereupon only the $K$1=1,"" part calculated caused an almost instantaneous recalculation, certainly much too fast for me to be manually time it. It follows therefore that the fastest way will be : =IF(ROW()-6$B$1,"",<Your formula) That way the only array formulas that will be calculate are those in the rows up to the value in B1 and they would have to have been calculated your way anyway. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "yshridhar" wrote in message ... Thank you Sandy. The formula works but not my problem. I have to copy down the formulae upto J1000. As these are array formulae it is taking some time to finish the calculation. What i want is a procedure that removes the formulae first from A7:J (previous copy range), and copies the formulae of A6:j6 down the value in B1. Any suggestions pls. Thank you once again Sandy for your formula. Regards Sridhar "Sandy Mann" wrote: If you mean for example with 6 in B1, for the 5 rows below A6:J6 to show a result of a formula then enter in A7:J7 a formula like: =IF(ROW()-6<$B$1,<Your formula,"") and copy down as far as you will ever need. The formulas will only show down as far ar the number of rows sprecified in B1. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "yshridhar" wrote in message ... Is there any procedure to copy down a range of formulae (say A6:J6) based on a cell value (b2)? I have formulae on a6:j6. I would like to copy down them upto the value that is entered in b2. Thank you all Sridhar |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy down a range of formulae
You're very welcome, Gladthat you got it worked out.
-- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "yshridhar" wrote in message ... Thank you Sandy for your explanation. After your suggestion i changed my data and reduced the formulae. Thanks alot Sridhar "Sandy Mann" wrote: Copying formulas down by VBA will be slow and then the array formula will still have to calculate so what you are asking for will be slower, not faster, except for small numbers of rows. As an experiment I created a formula in A1: =IF($K$1=1,"",SUMPRODUCT((M1:Z10000)*(N1:N10000))) and copied it across to J1 and then down to Row 1037 - some 10,370 formulas Deleting the 1 from K1 caused the SUMPRODUCT() part to calculate and it took about 10 minutes to calculate. Entering the 1 back into K1 whereupon only the $K$1=1,"" part calculated caused an almost instantaneous recalculation, certainly much too fast for me to be manually time it. It follows therefore that the fastest way will be : =IF(ROW()-6$B$1,"",<Your formula) That way the only array formulas that will be calculate are those in the rows up to the value in B1 and they would have to have been calculated your way anyway. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "yshridhar" wrote in message ... Thank you Sandy. The formula works but not my problem. I have to copy down the formulae upto J1000. As these are array formulae it is taking some time to finish the calculation. What i want is a procedure that removes the formulae first from A7:J (previous copy range), and copies the formulae of A6:j6 down the value in B1. Any suggestions pls. Thank you once again Sandy for your formula. Regards Sridhar "Sandy Mann" wrote: If you mean for example with 6 in B1, for the 5 rows below A6:J6 to show a result of a formula then enter in A7:J7 a formula like: =IF(ROW()-6<$B$1,<Your formula,"") and copy down as far as you will ever need. The formulas will only show down as far ar the number of rows sprecified in B1. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "yshridhar" wrote in message ... Is there any procedure to copy down a range of formulae (say A6:J6) based on a cell value (b2)? I have formulae on a6:j6. I would like to copy down them upto the value that is entered in b2. Thank you all Sridhar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy formats and formulae from 1 worksheet to all worksheets in f | Excel Discussion (Misc queries) | |||
formulae to copy cells from 1 worksheet and paste into another? | New Users to Excel | |||
copy between worksheets does not copy formulae just values | Excel Discussion (Misc queries) | |||
Copy formulae and paste to range with Office 2003 | Excel Discussion (Misc queries) | |||
Formulae using range names | Excel Worksheet Functions |