$$Excel-Macros$$ Macro - Open PowerPoint and Update Links?
Thanks in advance, experts!! I have a PowerPoint presentation that has links to Excel charts that need to be updated each time the Excel file is changed. I have everything working fine (I can update, save, and close the Excel file, open PP, and it prompts me to Update Links - I click yes, and it updates fine) What I want to be able to do is when I click a button on Excel, have it open the PP, close/save Excel, and update the links to charts in Excel. I have the code to open PP itself then close/save Excel, but when I open the PP file using this code, it never prompts to update the links (and it doesn't update). Here's the code I have so far: Dim objPPT As Object Set objPPT = CreateObject(PowerPoint.Application) objPPT.Visible = True objPPT.Presentations.Open \\ServerName\FileName.pptx ActiveWorkbook.Close True This opens PP, then saves/closes Excel - but I then have to navigate within the PP to update the links. The problem is, the Excel file has to be closed before the PP will update the links. I'm trying to make this all simple for all the users of it (open an Excel file, make a change, click a button - and it will save/close the Excel file, open a pre-designed PP file and update all links with data they just changed) - seems simple, but I can't figure out this last step! Even if they got the prompt they do when they open the file directly, that would be acceptable, but I can't figure out why when opening the PP file from this macro, it doesn't prompt to update the links within PP. Thanks for any help!! -- -- 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$$ Compare multiple tables - hopefully through a pivot table?
I guess I'm in over my head. I have no clue how to use what you just attached. Thanks anyway. On Sep 8, 9:30 pm, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Robert, Please see attached add-in. It will be help to Compare Table. On Fri, Sep 9, 2011 at 12:29 AM, Robert Jacobs robertjacob...@gmail.comwrote: Thanks in advance, experts! What I have: 2 worksheets in same Excel file, each has a table (Table1 on WS1, Table2 on WS2) with the SAME headers/type of data. As an example (only), BOTH tables have: Invoice# Cat1Sales Cat2Sales Cat3Sales TotalSales The only difference is that Table1 has this info for a single customer, Table2 has the info for multiple other customers. I would like to be able to bring it all into a pivot table (and have a chart) that will allow me to filter and calculate % differences between Table1 and Table2. For instance: Table1 shows that Cat1Sales make up 30% of all sales in the table (Cat2Sales 50%, Cat3Sales 20%), but for other customers (Table2), Cat1Sales make up 50% of all sales (Cat2Sales 10%, Cat3Sales 40%). This will show the customer how their purchasing direction varies from other customers', and maybe help them change their buying habits for more success. I would love to attach my existing file, but I can't seem to figure that out either... Thanks again for helping -- --- --- 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 -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ CompareWorksheetsAdd-in.xla 134KViewDownload -- -- 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$$ Compare multiple tables - hopefully through a pivot table?
Thanks in advance, experts! What I have: 2 worksheets in same Excel file, each has a table (Table1 on WS1, Table2 on WS2) with the SAME headers/type of data. As an example (only), BOTH tables have: Invoice#Cat1SalesCat2SalesCat3Sales TotalSales The only difference is that Table1 has this info for a single customer, Table2 has the info for multiple other customers. I would like to be able to bring it all into a pivot table (and have a chart) that will allow me to filter and calculate % differences between Table1 and Table2. For instance: Table1 shows that Cat1Sales make up 30% of all sales in the table (Cat2Sales 50%, Cat3Sales 20%), but for other customers (Table2), Cat1Sales make up 50% of all sales (Cat2Sales 10%, Cat3Sales 40%). This will show the customer how their purchasing direction varies from other customers', and maybe help them change their buying habits for more success. I would love to attach my existing file, but I can't seem to figure that out either... Thanks again for helping -- -- 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$$ Pivot Table Percent of Column...
Thanks to everybody for their suggestions. Fred - I couldn't layout my data the way you suggested, just due to the sheer number of rows it would have created. The data I provided was a joke compared to what is actually listed, and the number of categories is much, much more than the 3 I gave in my example. But I did keep your example in my resources for 'just in case' future spreadsheets. Daniel - I had found that page a while ago. As I mentioned in my post, it wasn't calculating the percent of the column that I wanted - it always gave me 100%. Thanks for the reply though. Viper - It took me a little digging (because your sample was EXACTLY what I needed) before I figured out how you accomplished what you accomplished. I did, however, figure it out, and it your methods work perfectly for what I needed. 5000 points to you, and thank you very much for your help. All of your suggestions are appreciated. Thank you for serving us little guys. You're awesome! On Aug 26, 6:31 am, §»VIPER«§ viper@gmail.com wrote: Hi please find the attachment it may help -- *Great day,* *viper * On Fri, Aug 26, 2011 at 2:13 PM, Daniel dcolarde...@free.fr wrote: Have a look at the page : http://support.microsoft.com/kb/106359/en-us Daniel -Message d'origine- De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de Robert Jacobs Envoyé : jeudi 25 août 2011 16:35 À : MS EXCEL AND VBA MACROS Objet : $$Excel-Macros$$ Pivot Table Percent of Column... Thanks in advance experts!!! This is very complicated to explain, but I'll give it a go: I have data (Invoice#, Invoice Year, Cat1 Sales, Cat2 Sales, Cat3 Sales), and need to find the percent of sales for each category, for each year in my data. For instance, if I have: Invoice# Year Cat1 Cat2 Cat3 123456 2006 $5.00 $4.00 $3.00 234567 2006 $5.00 $4.00 $3.00 345678 2006 $5.00 $4.00 $3.00 987654 2007 $3.00 $4.00 $5.00 876543 2007 $3.00 $4.00 $5.00 765432 2007 $3.00 $4.00 $5.00 I need a pivot table that will sum all values for each category by year, then tell me what percent of that year's sales belonged to that category. I can get a pivot table to show each category by year with total sales like this: Values 2006 2007 Cat1 $15.00 $9.00 Cat2 $12.00 $12.00 Cat3 $9.00 $15.00 BUT, I really need the percent of each category for that column. For instance, 2006 and 2007 both have $36.00 total sales. So I would want to know what percent of that $36.00 was for Cat1, Cat2, and Cat3. Something like this: Values 2006 2007 Cat1 42% 25% Cat2 33% 33% Cat3 25% 42% Does anybody have any recommendations? I have tried Show Value As Percent of Column, but it always shows 100%. If I Show Value As Percent of Row, it shows what percentage of the total sales of Cat1 was sold for each year. i.e. - Cat1 would show 62% for 2006 and 38% for 2007. Any help would be GREATLY appreciated!!! Thank you! -- --- - -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip2. 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.com5. 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 -- --- --- 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 sample.xls 14KViewDownload -- -- 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
$$Excel-Macros$$ Pivot Table Percent of Column...
Thanks in advance experts!!! This is very complicated to explain, but I'll give it a go: I have data (Invoice#, Invoice Year, Cat1 Sales, Cat2 Sales, Cat3 Sales), and need to find the percent of sales for each category, for each year in my data. For instance, if I have: Invoice#YearCat1Cat2Cat3 123456 2006$5.00 $4.00 $3.00 234567 2006$5.00 $4.00 $3.00 345678 2006$5.00 $4.00 $3.00 987654 2007$3.00 $4.00 $5.00 876543 2007$3.00 $4.00 $5.00 765432 2007$3.00 $4.00 $5.00 I need a pivot table that will sum all values for each category by year, then tell me what percent of that year's sales belonged to that category. I can get a pivot table to show each category by year with total sales like this: Values2006 2007 Cat1 $15.00 $9.00 Cat2 $12.00 $12.00 Cat3 $9.00 $15.00 BUT, I really need the percent of each category for that column. For instance, 2006 and 2007 both have $36.00 total sales. So I would want to know what percent of that $36.00 was for Cat1, Cat2, and Cat3. Something like this: Values20062007 Cat1 42% 25% Cat2 33% 33% Cat3 25% 42% Does anybody have any recommendations? I have tried Show Value As Percent of Column, but it always shows 100%. If I Show Value As Percent of Row, it shows what percentage of the total sales of Cat1 was sold for each year. i.e. - Cat1 would show 62% for 2006 and 38% for 2007. Any help would be GREATLY appreciated!!! Thank you! -- -- 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?
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
$$Excel-Macros$$ Filter criteria of one row - show previous row...
Thanks in advance to the expert that can help me I have an excel document with thousands of records, each record has a message associated with it that is listed on the row beneath that record... i.e. Record -SPR Office_Loc 123456 FS_FTW 234567 Message - Link is already active. No updates allowed SPR Record -SPR Office_Loc 654321 FS_FTW 765432 Message - From SKU is already in an existing linkage SPR Record -SPR Office_Loc 258456 FS_FTW 654852 Message - Link is already active. No updates allowed SPR What I need to do is filter this excel sheet to only show me the records with a message reading From SKU is already in an existing linkage SPR - I am very new to Excel, and know how to auto filter, but when I do that, I only get the Message line, and I really need the line above it... Can anybody 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 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
