$$Excel-Macros$$ Macro Help
Dear All, Please find attached a file. It contains four sheets. Sheet branches contains details for branches, sheet data contains data for the branches and sheet report contains the rough format in which I need the report to be in. My quest is that when ever a new branch is opened, for example, branch 23 in city A, a macro (or some sort of formula) runs, which automatically enters a row in the report sheet for city A data, copy pastes the formula from the above row and brings the data in the report sheet (There is no need to update any record in data sheet). The way the report may look after the macro runs is as shown in sheet report (2). A prompt reply will be highly appreciated. Best Regards, ZAC -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts Exp.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Pivot Problem
Dear All, I have to apply pivot on a data that reaches approx 1.5m rows and is spread on 15 columns. If I divide the data in two parts of 0.8m rows each, I can not do a complete analysis. Any suggestions?? Regards, ZAC -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Macro Required
Dear Group, I have excel sheets containing sales data on daily basis (from about 150 sites) (The columns are similar in all the daily sale files but rows change from time to time as new sites either become operational or stop working). I wish to consolidate all the data in one excel file in such a way that the macro extracts data from specific cells and inputs them into the consolidated sheet, Further, once one sheet is full in the consolidated sheet, the macro would automatically create a new sheet and start inputing the data there. If this task can be done easily with access, I'm all ear for access based suggestion as well. Regards, ZAC -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Formula Guide Require !!
Thanks Shayam for the link to the great site... But does anyone knows any site from where I can download the details of these functions.. I want to read them at my leasure time at home as it is impossible to go through them during the office hours.. Thanks ZAC On Tue, May 4, 2010 at 8:28 AM, rf1234 rf1234 rfhyd1...@gmail.com wrote: Hello Dear, Try the following link http://www.cpearson.com/excel/topic.aspx There is a details of all functions and formulas. On Mon, May 3, 2010 at 8:47 PM, D Kumar dkumar9...@gmail.com wrote: Hi All, I am not a perfect in excel but i have a little bit knowledge of some formulas, is anyone in group can help me to learn more about excel with ready reference. Regards Dheeraj S Chauhan ***All things are difficult before they are esay*** -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- Thanks Regards Shyam Software Engineer '' When one door of happiness closes, another opens; but often we look so long at the closed door that we do not see the one which has been opened for us. - Helen Keller '' Wenn eine Tür des Glücks schließt, öffnet sich ein weiteres, aber oft schauen wir so lange auf die geschlossene Tür, dass wir nicht sehen ein, die für uns geöffnet wurde. - Helen Keller '' -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Urgent Help Required in a macro
Thanks a bundle Dave. This has expedited my work even more. :-) (P.S) Can anyone refer me to a good course from where I can learn to write macros... On Wed, Apr 21, 2010 at 4:31 AM, Dave Bonallack davebonall...@hotmail.comwrote: Hi Zac, Can I suggest a simpler approach? Using the Ctrl key, select the 2 cells you want to swap, then use a shortcut key to run this code: Sub swap() If Selection.Count 2 Then Exit Sub A = ActiveCell.Value B = ActiveCell.Address For Each c In Selection c.Select Exit For Next c D = ActiveCell.Value ActiveCell.Value = A Range(B).Value = D End Sub It should be quicker than a dialogue box with 3 questions. Regards - Dave. -- Date: Tue, 20 Apr 2010 14:55:44 +0300 Subject: $$Excel-Macros$$ Urgent Help Required in a macro From: chaudhry.zahid@gmail.com To: excel-macros@googlegroups.com Hi Group, I'm working in a huge excel sheet with thousands of entries. I am currently looking for a macro, that when invoked (upon press of a key etc) would open a dialogue box having three fields. The first field being Select Row, In which I can write a row number Like 3, the second filed would contain Replace item from column, where I can write, for example G, and the third field containing an other Replace item from column for example AA. In this case the VBA code should select cells G3 and AA3. Than when I press enter or a button on the excel sheet the contents of cell G3 should go to AA3 and contents of AA3 should go to G3. The dialogue should not close after the cell contents have been replaced, but should close upon clicking the cross because there would be mutiple entries that need correction. An urgent reply and help would be highly appriciated, I have very little time to do tjis HUGE task of finding and replacing data in excel. Thanks in advance and Regards, ZAC. -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- Find it on Domain.com.au http://domain.com.au/ Need a new place to live?http://clk.atdmt.com/NMN/go/157631292/direct/01/ -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Urgent Help Required in a macro
Hi Group, I'm working in a huge excel sheet with thousands of entries. I am currently looking for a macro, that when invoked (upon press of a key etc) would open a dialogue box having three fields. The first field being Select Row, In which I can write a row number Like 3, the second filed would contain Replace item from column, where I can write, for example G, and the third field containing an other Replace item from column for example AA. In this case the VBA code should select cells G3 and AA3. Than when I press enter or a button on the excel sheet the contents of cell G3 should go to AA3 and contents of AA3 should go to G3. The dialogue should not close after the cell contents have been replaced, but should close upon clicking the cross because there would be mutiple entries that need correction. An urgent reply and help would be highly appriciated, I have very little time to do tjis HUGE task of finding and replacing data in excel. Thanks in advance and Regards, ZAC. -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Fwd: Urgent Help Required in a macro
Thanks Ayush, it works nicely now. I received a mail from your personal address and I replied without checing the address thats why this happened. Any how, will be more careful in the future. :-) Thanks again for all the help. On Tue, Apr 20, 2010 at 6:34 PM, ayush jain jainayus...@gmail.com wrote: Hi Chaudhary, Please note that you should keep our Group email address in all communications. It will help you. I have modified the macro to swap the values. Have a look and it should work now. Let me know if any further assistance is required. The logic based updation of data is possible and someone in group should help you out on same, Please submit a separate query with detailed example. Best regards, Ayush Jain Group Manager On Tue, Apr 20, 2010 at 8:38 PM, Chaudhry Zahid Ali chaudhry.zahid@gmail.com wrote: Dear Ayush, Thanks for the file. But the problem is that when it updates Data from A1 to B1, the data in one of the cell's changes but the data in the other cell remains the same. Hwat I need is, suppose, A1 has a value of 100 and B1 has a value of 200, when I click submit, B1 should show 100 and A1 should show 200. And yes you are right, the data that I have has a trend to follow, but the issue is that there are too many variables to follow. Still I am sending you two rows from the data (From over 7000 rows), the pink values need to be max within the seven day period, than comes green values and finally the blue ones. The white value preceeding the blue value needs to be less than the blue value and the white value after the green cells needs to be less than the green value. The other two values in the week can have any value but should be less than the value in highlighted color. I hope you get the idea. Thanks for sending me the macro and I'll highly appreciate if you can send me the adjusted macro or if you can send me any macro or formula which can follow and solve the problem in above para. Thanks and Kindest Regards, ZAC On Tue, Apr 20, 2010 at 5:27 PM, ayush jain jainayus...@gmail.comwrote: Hi ZAC, I have created a user form as per your needs. The shortcut is CTRL + Y and userform will pop up on this shortcut. The data will be updated on click of submit button. The fields will reset on data successfully updated. Please have a look and let me know if any further assistance is required. It appears to me that this can be done in much better way rather than updating each value. If you have any logic to update the data from one place to another, We can help you doing same. Do let us know with relevant data, if you have any questions. Best regards, Ayush Jain Group Manager On Tue, Apr 20, 2010 at 5:25 PM, Chaudhry Zahid Ali chaudhry.zahid@gmail.com wrote: Hi Group, I'm working in a huge excel sheet with thousands of entries. I am currently looking for a macro, that when invoked (upon press of a key etc) would open a dialogue box having three fields. The first field being Select Row, In which I can write a row number Like 3, the second filed would contain Replace item from column, where I can write, for example G, and the third field containing an other Replace item from column for example AA. In this case the VBA code should select cells G3 and AA3. Than when I press enter or a button on the excel sheet the contents of cell G3 should go to AA3 and contents of AA3 should go to G3. The dialogue should not close after the cell contents have been replaced, but should close upon clicking the cross because there would be mutiple entries that need correction. An urgent reply and help would be highly appriciated, I have very little time to do tjis HUGE task of finding and replacing data in excel. Thanks in advance and Regards, ZAC. -- Best regards, Ayush Jain -- Best regards, Ayush Jain -- Best regards, Ayush Jain -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- 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
Re: $$Excel-Macros$$ Re: Pivot Problem
Dear Michael, Thanks for the piece of advice. I'm basically new to the management accounting world. Please do not take this as if I am new to finance and accounting (But my excel skills are resticted and have no knowledge of access yet). That said, can you guide me to some tutorials etc or books from where I can jump start into this huge ocean. Kindest Regards. Chaudhry On Tue, Apr 13, 2010 at 11:58 AM, MD18358 michael.e.dray...@citi.comwrote: Hi Chaudhry, I hate to commit sacrilege in the Excel VBA sight but its sounds like excel is probably not the right solution for such a massive task. Access can handle larger database queries. Even if you use Access to perform all your summerizing and then export it back to excel for the presentation parts, I think you you come out with far less stress. -Good Luck Michael On Apr 12, 2:41 pm, Chaudhry Zahid Ali chaudhry.zahid@gmail.com wrote: Hello Group, *Overview:* I have a issue to discuss. I have got an excel sheet which contains over 150,000 trial balance account subheads made up of data from different branches (All Branches). So there are multiple account heads that are repeating. I have constructed a Pivot table out of the data which on application of a filter gives me NP and Sales amount for different types (Huge, Big, Medium, Small) of branches. *Required:* Now what I want to do is to apply two different filters on two different columns which fall under the same Pivot table. Is it possible, or am I asking too much from excel. I know I can construct a seperate table for both NP and sales, but the issue is I am trying to automate the process and if I make seperate account heads for seperate files, well, I will be ending up making atleast 50 Pivot tables in a file which has already crossed 200 MB. *In Short:* *HELP* ** Thanks.. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe, reply using remove me as the subject. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Pivot Problem
Hello Group, *Overview:* I have a issue to discuss. I have got an excel sheet which contains over 150,000 trial balance account subheads made up of data from different branches (All Branches). So there are multiple account heads that are repeating. I have constructed a Pivot table out of the data which on application of a filter gives me NP and Sales amount for different types (Huge, Big, Medium, Small) of branches. *Required:* Now what I want to do is to apply two different filters on two different columns which fall under the same Pivot table. Is it possible, or am I asking too much from excel. I know I can construct a seperate table for both NP and sales, but the issue is I am trying to automate the process and if I make seperate account heads for seperate files, well, I will be ending up making atleast 50 Pivot tables in a file which has already crossed 200 MB. *In Short:* *HELP* ** Thanks.. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe, reply using remove me as the subject.
$$Excel-Macros$$ Date Problem
Hi, I have got a little problem that I need to solve I have a sheet with multiple transactions. The columns include Date and amounts. There are multiple amounts against a single date I need to extract the amounts which are appearing against every date, but the last amount in every date. As an example I'm attaching a sheet which has 5 dates and am highlighting the amounts that I need. Can anyone give me a formula to work with. The original sheet has 1+ rows Looking for a prompt reply Thanks everyone --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com If you find any spam message in the group, please send an email to Ayush @ jainayus...@gmail.com -~--~~~~--~~--~--~--- zz.xls Description: MS-Excel spreadsheet