Re: $$Excel-Macros$$ Need a formula for getting the output of one calculation as RATIO
Dear Rajan, Thanks for your formulait works. But I want to know, will there be any macro or VBA code for the query like this. Kindly help me if anything available like such. Thank you all... Regards, Saroj. On Sat, May 21, 2011 at 10:00 AM, Rajan_Verma rajanverma1...@gmail.comwrote: Hi Kabir, See if it help. *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Kabir:-) *Sent:* Friday, May 20, 2011 5:09 PM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Need a formula for getting the output of one calculation as RATIO Dear Gurus, I need a formula for calculated two numbers and resulted output should be in a Ratio.i.e., 2:1 or 1.5:3. Thank you in advance, Regards, Saroj. -- -- 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
Re: $$Excel-Macros$$ Help on Transpose Function based upon criteria
Dear Kaushik, I am having a small doubt pertaining to your solution given for transpose formula. When i look into the sheet, the claim no. 1 shows the Amount of 10, 10, 50 20 was been given. But as per your formula, the Amount of 10, 30 20 has got transpose to the Claim NO.1. Where as Amount Rs.30 belongs to Claim No.3. Hope I am not in a wrong understanding of the solution to point out this. Requested you to clarify my doubt, as this may be helpful in my work. Thank you, Regards, Saroj. On Wed, May 18, 2011 at 8:19 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Kaushik, see if it help On Wed, May 18, 2011 at 4:06 PM, KAUSHIK SAVLA savla.kaus...@gmail.comwrote: Hi All, I want to transpose certain data from row to column if meets specific criteria: eg Whole data is as below: Claim No. Amount 1 10 2 20 1 30 1 10 2 20 I want as Claim No. 1 10 30 10 I want transpose to happen if meets criteria that it match the claim number, i have 5 records as such. I have attached an spreadsheet as example Please help its urgent. Regards, Kaushik -- -- 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 -- Thanks regards, Noorain Ansari -- -- 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
Re: $$Excel-Macros$$ Need a formula for getting the output of one calculation as RATIO
Dear Noorain, I will be thankful for your effort on my request. Regards, Saroj. On Fri, May 20, 2011 at 10:39 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Pls try it =CONCATENATE(A1,:,B1) On Fri, May 20, 2011 at 5:08 PM, Kabir:-) saroj.32...@gmail.comwrote: Dear Gurus, I need a formula for calculated two numbers and resulted output should be in a Ratio.i.e., 2:1 or 1.5:3. Thank you in advance, Regards, Saroj. -- -- 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 -- Thanks regards, Noorain Ansari -- -- 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
Re: $$Excel-Macros$$ Need a formula for getting the output of one calculation as RATIO
*Try this Function* Function Ratio2(*ParamArray* arr()) As String Dim Arr2(1) As Integer For i = 0 To UBound(arr) Ratio2 = Ratio2 arr(i) / WorksheetFunction.Gcd(arr) : Next Ratio2 = Left(Ratio2, Len(Ratio2) - 1) End Function On Mon, May 23, 2011 at 12:35 PM, Kabir:-) saroj.32...@gmail.comwrote: Dear Rajan, Thanks for your formulait works. But I want to know, will there be any macro or VBA code for the query like this. Kindly help me if anything available like such. Thank you all... Regards, Saroj. On Sat, May 21, 2011 at 10:00 AM, Rajan_Verma rajanverma1...@gmail.comwrote: Hi Kabir, See if it help. *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Kabir:-) *Sent:* Friday, May 20, 2011 5:09 PM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Need a formula for getting the output of one calculation as RATIO Dear Gurus, I need a formula for calculated two numbers and resulted output should be in a Ratio.i.e., 2:1 or 1.5:3. Thank you in advance, Regards, Saroj. -- -- 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 -- Regards Rajan verma +91 9158998701 -- -- 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
Re: $$Excel-Macros$$ Need a formula for getting the output of one calculation as RATIO
Amended.. Function Ratio2(*ParamArray* arr()) As String For i = 0 To UBound(arr) Ratio2 = Ratio2 arr(i) / WorksheetFunction.Gcd(arr) : Next Ratio2 = Left(Ratio2, Len(Ratio2) - 1) End Function On Mon, May 23, 2011 at 8:16 PM, rajan verma rajanverma1...@gmail.comwrote: *Try this Function* Function Ratio2(*ParamArray* arr()) As String Dim Arr2(1) As Integer For i = 0 To UBound(arr) Ratio2 = Ratio2 arr(i) / WorksheetFunction.Gcd(arr) : Next Ratio2 = Left(Ratio2, Len(Ratio2) - 1) End Function On Mon, May 23, 2011 at 12:35 PM, Kabir:-) saroj.32...@gmail.comwrote: Dear Rajan, Thanks for your formulait works. But I want to know, will there be any macro or VBA code for the query like this. Kindly help me if anything available like such. Thank you all... Regards, Saroj. On Sat, May 21, 2011 at 10:00 AM, Rajan_Verma rajanverma1...@gmail.comwrote: Hi Kabir, See if it help. *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Kabir:-) *Sent:* Friday, May 20, 2011 5:09 PM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Need a formula for getting the output of one calculation as RATIO Dear Gurus, I need a formula for calculated two numbers and resulted output should be in a Ratio.i.e., 2:1 or 1.5:3. Thank you in advance, Regards, Saroj. -- -- 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 -- Regards Rajan verma +91 9158998701 -- Regards Rajan verma +91 9158998701 -- -- 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
$$Excel-Macros$$ Automation of formula
The column difference highlighted in green is calculated as the difference between the two cells to its left.Then %difference is the percentage in their difference and abs difference is their absolute difference. How to code such that if i pass the formula these three fields are calculated for rest of the columns. attached is the worksheet. -- -- 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 EXCEL_ACCESS.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Find Macth Unmatch details
hi , I need your help. Below are my requirements I have 3 different spreadsheets. month.xls Checklist.xls Master.xls a.Master.xls has 5 diff sheets, I need to filter extract details from each sheet paste it in new spreadsheet.I need to extract the details of employee with designation Associate from each sheet paste it in new sheet b.Extracted/Filtered details will be then be compared with month.xls data on a common column Emp name.Then matched and unmatched details will be pasted in either new spreadhsheet or new sheet with flag match or unmatch in next column. Attached please find the file for your pursual This macro should run on both Excel 2003 and 2007 Please let me know if you need any additional information. Thanks, Sandeep -- Sandeep Kadam -- -- 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 Master.xls Description: MS-Excel spreadsheet May.xls.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Update pivot table filters based on already applied filters?
I access this from Google Groups... don't see an attach option. Email possibly? I have found the PivotTableSelectionChange() event, I just don't know what to add here. I've tried the refresh pivot table code, but it doesn't refresh the selection list. Again, my data's not actually changing, I just want the options in the selection lists for each filter (they are all report filters) to reflect filters already applied. For instance, if I filter by sales territory, I would like the customer name filter to only include names for the sales territory selected. It shows all names now - if I click on a customer name that doesn't belong to the currently selected sales territory, it just shows a blank chart. Help is appreciated Thanks! On May 20, 11:37 pm, ashish koul koul.ash...@gmail.com wrote: can you attach the sample file On Sat, May 21, 2011 at 1:31 AM, Robert Jacobs robertjacob...@gmail.comwrote: Thank you in advance, experts! (also posted on Microsoft Excel group, but not as many members, nor as many responses as I see here) I have an Excel file with a couple of pivot tables. Everything works perfectly, but I would like one more piece of functionality, if it's possible. My raw data includes 6 columns, which are Cust#, CustName, SalesRep, InvAmount, InvDate, and CustCategory. When I go to my pivot table, I have it setup like: Report Filters: Cust#, CustName, SalesRep, CustCategory Row Labels: InvDate Values: Sum of InvAmount When I apply a filter, such as I display all information regarding the SalesRep, the filter works perfectly, showing me only InvDates and InvAmount sums for that SalesRep. BUT, when I want to apply a second filter, the filter list itself still contains all Cust#, CustName, and CustCategory options for all sales reps. Is it possible to filter the list once a filter has been applied, so if I filter down to a single SalesRep, I only see his/her accounts listed in the other filter lists? Man, that was hard to word, and I still don't know if I got it right... PLEASE HELP if possible!!! Thanks guys -- --- --- 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 athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://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 -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- 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
Re: $$Excel-Macros$$ Update pivot table filters based on already applied filters?
Well after a bit of searching, it's looking like the Report filters are not dependent. No matter what you select in one report filter, every record is still listed in the other filters, even if they won't show any data when selected. Apparently there's no real way to do this, other than upgrading to Excel 2010 (for every person using this document) and deploying Slicer... Thanks for looking, but I guess this is impossible (wish it wasn't as it would be EXTRAORDINARALLY helpful) -- -- 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
Re: $$Excel-Macros$$ Keep formula in cell
Any suggestions on how to write this macro? I'm kinda new to macros. Thanks in advance! On May 23, 1:47 am, STDEV(i) setiyowati.d...@gmail.com wrote: when you type a data ( event a dot ) in a cell, the old data (incl. FORMULA) in the cell will be removed and replaced with the new data. with macro you can automatically keep the old data (and stored in another place) and then enter the new value in the same cell cmiiw On Mon, May 23, 2011 at 5:21 AM, Bob bobandrich...@comcast.net wrote: Is there a way to keep the formula in the cell when you enter your own value? Thanks in advance- Hide quoted text - - Show quoted text - -- -- 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
Re: $$Excel-Macros$$ Update pivot table filters based on already applied filters?
can you email you file to excel-macros@googlegroups.com and attach the workbook On Mon, May 23, 2011 at 9:58 PM, Robert Jacobs robertjacob...@gmail.comwrote: Well after a bit of searching, it's looking like the Report filters are not dependent. No matter what you select in one report filter, every record is still listed in the other filters, even if they won't show any data when selected. Apparently there's no real way to do this, other than upgrading to Excel 2010 (for every person using this document) and deploying Slicer... Thanks for looking, but I guess this is impossible (wish it wasn't as it would be EXTRAORDINARALLY helpful) -- -- 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 -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ http://akoul.posterous.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- 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
