• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto Copy Formula

N

nnavarro

Member
I created a sheet with many Vlookups, left and right, and mathematical formulas. The formulas don’t change. People dump data into this sheet and the look ups do the rest. Because this is a weekly YTD exercise the number of rows changes all the time. You dump a new file each month and drag the formulas down for each column (9 in total). Is there a trick or macro that would do this automatically? I mean if I add a new item can the formulas be copied automatically.


Your help would be greatly appreciated.


Noah Navarro
 
Noah


If the formulas are directly adjacent to the data without a gap

Select the Bottom Row of the Formulas and double click on the small black box at the Lower Right Corner


If the data is separated you will need to use some VBA.


Can't you just copy down say 1000 rows and leave the formulas in place


For more specific help can you upload a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

With some notes on the data processing flow
 
Hi, mnavarro!


There's another approach with a workaround for gaps and multiple cells copied simultaneously. Let the Worksheet_Change event enter the game.


Here is the link to the sample file:

https://dl.dropbox.com/u/60558749/Auto%20Copy%20Formula%20%28for%20mnavarro%20at%20chandoo.org%29.xlsm


It looks for a trigger event in columns A and C (const ksTriggerRange) and copies the formulas for columns B, D, E and H (const ksFormulaRange). Uses column A (kiWitnessColumn) for not copying if deleting values.


Just advise if any issue.


Regards!
 
Excel Ninja, Thank you so much for the help. Actually I look at the file that SirJB7 and it was exactly what I needed. It works like a charm. Thanks to both of you.


nnavarro
 
Hi SirJB7


I was also looking something like this but my data is being dump on sheet my another macro.

In this solution macro is being trigger by entering value in A coloum and if you copy/paste a range it do not get triggered.


-----------------------------------------MY CASE----------------------------------------

Is it possible to modified this macro in such a way that it will check for the rows where the formula exist and from next row it will drag down the formula to required depth.


For example 500 rows have formula so it will start formula from 501 to the down the line up to rows equal to a number returned from a cell Like [B1 =(COUNTA(A:A)]. User will also define the coloum where such dragdown is needed as it may not required to dragdown all coloum formulas.


It is required to optimize the calculation time for excel as having formula for 50000 rows while input may vary from 1000 rows to 50000 rows will waste a lot of time for smaller input data.


Regards,
 
Hi, mnavarro!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!
 
Hi, Kuldeep!


If your another macro doesn't handle Worksheet_Change event it should still work. Actually no other standard events, as they're disabled at beginning to avoid Change events recursion.


After your "MY CASE", first paragraph is exactly what this macro does, but instead of looking for the equal sign as first char in cell formula it uses a manually set columns list.


Upload sample file(s) which include your another macro to see how to handle it.


BTW, as this is a cell by cell operation, don't try to be too strict with calculation time. Specially if you talk of thousands of rows.


Regards!
 
Thanks for quick response...Should we continue on this post or move to original one that i asked at http://chandoo.org/forums/topic/use-of-formula-in-cells-where-it-is-needed


I am happy as after that post calculation time is reduced to 5 time by the kind help of Hui. I just could not resist my self asking as this question was just in line of my requirement. You can get the sample file from http://sdrv.ms/N8IqTZ.


I just want to have a template file for 1000 rows and should be increased if input data is increased.


Sorry this sample file do not have macro....it has just the data which is generated by macro to calculate.


Regards,
 
Hi, Kuldeep!


Without the macro (whether in this workbook or if in another with that workbook too) I'm afraid it'll be difficult to help you.


Regards!
 
Hi SirJB7,


Please find the link for file having macro. Macro name is Copy_from_Raw_Data() written by NarayanK. https://docs.google.com/open?id=0B0KMpuzr3MTVWkhxTWVPUGltTHM


This macro will paste the data on desired files and this will be input for the file available at http://sdrv.ms/N8IqTZ.


Regards,
 
Hi, Kuldeep!

I have no access to the skydrive file (last one).

Regards!


EDIT:


Despite of that, in the google docs file (first one), which I could download and open, the macro Copy_From_Raw_Data operates from sheet 'Raw data' to sheet 'Filtered data', so I have this questions:

a) if your intended sheet to apply my macro is 'Filtered data' I think you should give a try and copy the whole code from my uploaded file in sheet Hoja1 to this worksheet window pane

b) if a) is correct, I don't understand what does the second file in this process

c) however, the macro Copy_From_Raw_Data doesn't fulfill, paste or add values outside its own workbook
 
Hi SirJB7,


I am sorry that you could not down lode the file but you are in right direction.


Answer for,C)


My data will be generated on filtered data sheet which is the part of workbook having macro "Copy_From_Raw_Data"


Answer for (B)


Now filtered data may have 500 rows or 50K rows on the the sheet generated via macro "Copy_From_Raw_Data". I have set-up formulas in separate sheet of same workbook which should have formula for the maximum data expected on filtered data sheet.


Now if I set-up (sumproduct or countif) for say 50K row it will take considerable time while having formula on 1K row will no serve the propose thus I was expecting that if can make a template for 1K rows and a macro can drag & drop formula to desired depth.


Answer for (A)


Due to calculation times I have disabled the automatic calculations hence I will trigger by F9 or a specific key-set.


P.S. Please consider that all data is in single workbook.


Regards,
 
Hi, Kuldeep!

Worksheet_Change events aren't tied to Calculation mode, so you can proceed with my a) suggestion of copying the whole code from my sample file to your 'Filtered data' sheet and check execution time. However, created by macros or manually use of SUMPRODUCT on 50K row will always be slow, regardless the use of my macro, of any other macro or manual input.

Regards!
 
<div class="ProductImage"><img class="listingProductImage" title=" Karen Millen Outlet Colourblock Bandage Dress Green/Multicolour " alt="Karen Millen Outlet Colourblock Bandage Dress Green/Multicolour" src="http://www.karen-millen-outlets.co.uk/bmz_cache/1/17eadfadfd6c7af55387f5b45925bf11.image.125x200.jpg" width="125" height="200" /></div>

<div class="ProductDetails">Karen Millen Outlet Colourblock Bandage Dress Green/Multicolour</div>

<!--

<div class="listingDescription">WELCOME TO www.karen-millen-outlets.co.uk Description: * Colourblock bandage knit dress with draped jersey front * 1% Elastane,12% Polyamide,87%...</div>

-->

<div style="height: 18px"><span class="pro_price"><span class="normalprice">£172.79 </span> <span class="productSpecialPrice">£81.61</span></span></div>

<div class="ProductActionAdd">Choose Options</div>


[*]
<div class="ProductImage"><img class="listingProductImage" title=" Karen Millen Outlet Colourblock Dress Red " alt="Karen Millen Outlet Colourblock Dress Red" src="http://www.karen-millen-outlets.co.uk/bmz_cache/8/8ce80a8b4c09dd69a05d15e1b4178939.image.125x200.jpg" width="125" height="200" /></div>

<div class="ProductDetails">Karen Millen Outlet Colourblock Dress Red</div>

<!--

<div class="listingDescription">WELCOME TO www.karen-millen-outlets.co.uk Description: * Stretch cotton colourblock full skirted shift dress * 15% Polyamide,4% Elastane,81% Cotton...</div>

-->

<div style="height: 18px"><span class="pro_price"><span class="normalprice">£172.79 </span> <span class="productSpecialPrice">£86.71</span></span></div>

<div class="ProductActionAdd">Choose Options</div>


<div class="ProductImage"><img class="listingProductImage" title=" Karen Millen Outlet Colourblock Satin Dress Blue/Multicolour " alt="Karen Millen Outlet Colourblock Satin Dress Blue/Multicolour" src="http://www.karen-millen-outlets.co.uk/bmz_cache/f/f97c762e796f246a4e49242999a44cf8.image.125x200.jpg" width="125" height="200" /></div>

<div class="ProductDetails">Karen Millen Outlet Colourblock Satin Dress Blue/Multicolour</div>

<!--

<div class="listingDescription">WELCOME TO www.karen-millen-outlets.co.uk Description: * Signature stretch satin colourblock halterneck dress with fish tail style hem * 22%...</div>

-->

<div style="height: 18px"><span class="pro_price"><span class="normalprice">£172.79 </span> <span class="productSpecialPrice">£81.61</span></span></div>

<div class="ProductActionAdd">Choose Options</div>

[*]
<div class="ProductImage"><img class="listingProductImage" title=" Karen Millen Outlet Colourblock Satin Dress Red/Multicolour " alt="Karen Millen Outlet Colourblock Satin Dress Red/Multicolour" src="http://www.karen-millen-outlets.co.uk/bmz_cache/5/50eada7849a0e4d959194646591aca20.image.125x200.jpg" width="125" height="200" /></div>

<div class="ProductDetails">Karen Millen Outlet Colourblock Satin Dress Red/Multicolour</div>

<!--

<div class="listingDescription">WELCOME TO www.karen-millen-outlets.co.uk Description: * Signature stretch satin colourblock halterneck dress with fish tail style hem * 22%...</div>

-->

<div style="height: 18px"><span class="pro_price"><span class="normalprice">£172.79 </span> <span class="productSpecialPrice">£81.61</span></span></div>

<div class="ProductActionAdd">Choose Options</div>


相关的主题文章：
 
You must log in or register to reply here.
Back
Top