Its works. Thanks Dave. On Sun, Apr 24, 2011 at 5:50 PM, Dave Bonallack <davebonall...@hotmail.com>wrote:
> Hi Yogesh, > I only have one other idea to stay with your worksheet functions. All your > Countifs functions contain whole column references. > > eg: =COUNTIFS(DB_Sheet!*$D:$D*,$A16,DB_Sheet!*$E:$E*,K$2,DB_Sheet!*$B:$B* > ,$A$74) > > Since none of your DB_Sheet data goes beyond row 288325, you could change > EVERY formula: eg: > > =COUNTIFS(DB_Sheet!*$D1:$D300000*,$A16,DB_Sheet!*$E1:$E300000* > ,K$2,DB_Sheet!*$B1:$B300000*,$A$74) > > This MAY reduce the time significantly. > Regards - Dave. > > ------------------------------ > Date: Sun, 24 Apr 2011 09:03:05 +0530 > Subject: Re: $$Excel-Macros$$ Help me in this > From: yashr...@gmail.com > To: excel-macros@googlegroups.com > > Thanks Dave, for your suggestion. but problem is that i want to update all > sheets on daily basis. If i'll do this, it also be take same time. If you > have any other idea please share with me. I am also working on it if i will > get any solution then i will tell you. > > Thanks & Regards > Yogesh Gohil > > On Sat, Apr 23, 2011 at 6:44 PM, Dave Bonallack <davebonall...@hotmail.com > > wrote: > > Hi Yogesh, > At first glance, I'd say that you just have too many formulas in the > workbook, and that most of the calculations should probably be done by VBA > rather than worksheet functions. There appear to be some UDF's, but the VBA > is protected, so I can't examine them, or make any decision on how to speed > up the process. > I think I would populate each sheet separately with VBA, and only update > each sheet when it is activated, using a Worksheet_Activate event. > However, it may be worth trying the following before going wholesale into > VBA. > Each sheet has an EnableCalculation property. In VBA, set each sheet to > False when the workbook opens. The code would go into the ThisWorkbook > window, as a Workbook_Open event. > Something like: > > For each s in Worksheets > s.EnableCalculation = False > Next s > > Then, in the sheet window for each sheet, as a Worksheet_Activate event, > something like: > > Activesheet.EnableCalculation = True > Activatesheet.Calculate > > And as a Worksheet_Deactivate event, something like: > > Activesheet.EnableCalculation = False > > This way, a worksheet is only updated when needed. It still may take too > long, but it's simple to do, and worth a try. > But I still think VBA could do each sheet in seconds, not hours. > > Regards - Dave. > ------------------------------ > Date: Sat, 23 Apr 2011 09:07:55 +0530 > Subject: $$Excel-Macros$$ Help me in this > From: yashr...@gmail.com > To: excel-macros@googlegroups.com > > Hi Experts, > > Actually, this waterfall made by me but its take 3hour to complete. Please > help me out to make easy and time reducer format. > This format have Excel function and VBA coding. > > *For the attachment you can click on* > http://www.megaupload.com/?d=8KTIVGZX > > Thanks & Regards > Yogesh Gohil > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > > > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
