Excel guru's Urgent help

I have a bunch of charts created in Excel97 that are driven from pivot
 tables on another sheet.  When the workbook is opened in Excel 2000
 everything works fine except when you change criteria causing a macro
 to
 change the pivot tables, the source data range does not automatically
 reset as it does in 97.  I have to manually click on Source Data,
 click
 in the pivot table, and the range is reset, fixing the chart.  I don't
 know much VBA and I would like to hang a sub at the end of the macro
 that would automatically reset the source range.  Can someone help
 with
 the script?
 
 I created a chart in excel 2002 based on pivottable on the same page
 it plots the correct point but the only problem is if I change the
 period from 12 months to 6 month it is not resizing the chart table it
 show blanks in the rest of the months.
 
  Thanks
0
syedasker (4)
9/4/2003 4:11:20 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
261 Views

Similar Articles

[PageSpeed] 39

It sounds like the chart series are looking for data in an unchanging 
range, but the pivot table changes its size.  You could set up dynamic 
ranges in the sheet, and base your charts on these ranges, without the 
need for attacking the charts with VBA.  I have a couple of examples on 
my site, plus a page full of links:
   http://www.geocities.com/jonpeltier/Excel/Charts/Dynamics.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

syedasker@yahoo.com wrote:
> I have a bunch of charts created in Excel97 that are driven from pivot
>  tables on another sheet.  When the workbook is opened in Excel 2000
>  everything works fine except when you change criteria causing a macro
>  to
>  change the pivot tables, the source data range does not automatically
>  reset as it does in 97.  I have to manually click on Source Data,
>  click
>  in the pivot table, and the range is reset, fixing the chart.  I don't
>  know much VBA and I would like to hang a sub at the end of the macro
>  that would automatically reset the source range.  Can someone help
>  with
>  the script?
>  
>  I created a chart in excel 2002 based on pivottable on the same page
>  it plots the correct point but the only problem is if I change the
>  period from 12 months to 6 month it is not resizing the chart table it
>  show blanks in the rest of the months.
>  
>   Thanks

0
jonpeltier (303)
9/7/2003 1:58:14 AM
Jon Peltier <jonpeltier@yahoo.com> wrote in message > It sounds like the chart series are looking for data in an unchanging 
> range, but the pivot table changes its size.  You could set up dynamic 
> ranges in the sheet, and base your charts on these ranges, without the 
> need for attacking the charts with VBA.  I have a couple of examples on 
> my site, plus a page full of links:
>    http://www.geocities.com/jonpeltier/Excel/Charts/Dynamics.html
> 
> - Jon
> -------
> > _______
> 
> syedasker@yahoo.com wrote:
> > I have a bunch of charts created in Excel97 that are driven from pivot
> >  tables on another sheet.  When the workbook is opened in Excel 2000
> >  everything works fine except when you change criteria causing a macro
> >  to
> >  change the pivot tables, the source data range does not automatically
> >  reset as it does in 97.  I have to manually click on Source Data,
> >  click
> >  in the pivot table, and the range is reset, fixing the chart.  I don't
> >  know much VBA and I would like to hang a sub at the end of the macro
> >  that would automatically reset the source range.  Can someone help
> >  with
> >  the script?
> >  
> >  I created a chart in excel 2002 based on pivottable on the same page
> >  it plots the correct point but the only problem is if I change the
> >  period from 12 months to 6 month it is not resizing the chart table it
> >  show blanks in the rest of the months.
> >  
> >   Thanks

Jon thanks for replying
Even the table size is not changing table will show the required data
but it will not select that required data instead of that it will
select everthing in the table and chart will not expands.
0
syedasker (4)
9/24/2003 5:46:34 PM
Reply:

Similar Artilces:

Excel Adding years or months to a date
I want to be able to add months or years to a date. For example 25/4/05 + 9 months 25/4/05 + 15 years. I am using Excel 2000. The standard way is: =DATE(YEAR(A1),MONTH(A1)+9,DAY(A1)) to add 9 months. For 15 years try: =DATE(YEAR(A1)+15,MONTH(A1),DAY(A1)) HTH Jason Atlanta, GA "Joan" wrote: > I want to be able to add months or years to a date. > > For example 25/4/05 + 9 months > 25/4/05 + 15 years. > > I am using Excel 2000. On Mon, 25 Apr 2005 06:54:03 -0700, "Joan" <Joan@discussions.microsoft.com> wrote: >I want to be able to a...

help help help IF function
i am new to excell and need help with a problem...... the problem is iam looking for a amount < = 3000.00 and iam to display in bold and in light green i have never had to deal with that i got as far as =IF<=3000 then iam lost or it that even right?? I'm far from a whiz at Excel but I'd use conditional formatting. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "kimmy" <kimmy@discussions.microsoft.com> wrote in message news:ADC77884-FC87-47A3-90F2-D2AAF957F5D1@microsoft.com... >i a...

work order form and report design help
I am building an automotive shop database. I have a table that lists all of the services and the prices. I have the main form created where all of the customer data is entered in and I have another form that builds the work order for the customer. How do I build the form to allow for different services for the same customer without having to create service type 1, service type 2, etc. I need them to be independent of each other and I then need to create a report to print out for the customer. On Tue, 23 Mar 2010 08:24:01 -0700, Jimmy <Jimmy@discussions.microsoft.com> w...

Excel working in with Outlook
I have Office 2007 so obviously Outlook 2007 and Excel 2007. I have made up a newbie's Excel spreadsheet for my business. One thing to open that has my entire financial year's data in it and my car logbook. One thing I am going to add to it is parts. I want to know when parts need to be re-ordered so will set a level where I want Excel to show up basically something like a "reorder now" warning. However, I am often in a hurry, record the event and don't look at the outcome until I have some time, late at night. At that time, reordering becomes a nightmare. Is ther...

Graph Help- 12 month graph, but only want months that have passed
I need help setting up a graph. It is a simple bar graph, that has the months of the year on the x axis, and production in tons on the y axis. I have a table setup for this Jan to Dec, but obviously only have data for Jan through May. Is there a way I can set up the graph so it will only make the graph Jan through May, but as I add more data, and months go by, it will automatically add a new column for June, then July and so on and so on? I do not want the months that have no data to be graphed with a value of zero, I would rather them show up automatically when that month has data....

Help with Html Help in MFC
Hi; I am using Visual Studio .net 2003. I have created a CDhtmldialog. I also created a html help file using Html help workshop. According to some source from web saying that I can call this method: BOOL HtmlHelp( HWND hWndMain, LPCTSTR lpszHelp, UINT uCommand, DWORD dwData ), directly from anywhere in my code. So I include the htmlhelp.h and htmlhelp.lib to the project. But I don't know how to call this method, since there is a another htmlhelp method in Cdialog too: HtmlHelp( DWORD_PTR dwData, UINT nCmd = 0x000F ); Please give me a hand. Thx in advance. Pan Wilson. ...

excel export available
I have a query that I wish to export the results to Excel. However, the Excel button on the Export Data tab is grayed out? It used to work. Any ideas of what makes the Excel Export unavailable? Thanks, Mike ...

Creating an Excel table from Access
Hi, I have an Access database which I use to log downtime for systems. I have a requirement to produce a monthly report based on this data, however, this needs to be exported to excel in a specific format. Down the left side of the report need to go the names of the services, with the days (numerically like: 1, 2, 3 .. 29, 30 etc) across the top. Then I need to count the number of times each service was down on a give day, and insert that information into the necessary cell - so if intranet services had been down twice on 16th March, for example, there would be a 2 in column 16, whi...

Import contacts from Excel
I have numerous contact lists with various categories and wondered if i can import them into an email distribution list so that i do not need to hand type each email? thanks "angie" <angie@discussions.microsoft.com> wrote in message news:64042F86-62E0-4D42-B9CE-49C134DB4B7B@microsoft.com... >I have numerous contact lists with various categories and wondered if i can > import them into an email distribution list so that i do not need to hand > type each email? You can't import into a DL. Instead import into your Contacts and assign categories to ...

How to lock a line put on a graph in excell
I am trying to attach a line to a graph with months on the X axis. When I add additional months I want the line to stay in between the original months, however, it always moves as the graph expands. Drawing objects cannot be linked to specific values of chart axes, unless you incorporate them as series in the chart. Try the techniques here: http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Dennis" <Dennis@discussions.microsoft.com> wrote in message news:336...

Excel is not counting
merry x'mas In excel 2003, i entered a number in a column and dragged down the rows to count the consecutive numbers automatically, but it was just copying the same number instead counting. How to change so as to count? -- Life isa journey not a destination Do the same while holding the CTRL Pressed. Micky "Sherees" wrote: > merry x'mas > In excel 2003, i entered a number in a column and dragged down the rows to > count the consecutive numbers automatically, but it was just copying the same > number instead counting. How to change so as to...

Random Sampling in Microsoft Excell 2002
Hi, I'm trying to create a random sample using the Data Analysis tool in Excell. The problem I'm having is that I can't figure out how to make a non-repeating sample. I want all the observations in the sample to be unique. How do I do this without manually deleting the repeat entries on the output worksheet? next to the entries, put in the formula =Rand() then drag fill down the column. Sort the data and this column with this column as the key. Then take the top "n" items. Regards, Tom Ogilvy "EJ Ford" <edseljoe@earthlink.net> wrote in mess...

Excel 2003 - VBA
Hi, What is a simple way to check to see if a sheet of a given name is present in a workbook? Thanks, Craig Dim Sht as object set sht = nothing on error resume next set sht = activeworkbook.sheets("somesheetnamehere") on error goto 0 if sht is nothing then msgbox "nope" else msgbox "yep" end if Craig Brandt wrote: > > Hi, > > What is a simple way to check to see if a sheet of a given name is present > in a workbook? > > Thanks, > Craig -- Dave Peterson Dave: Thanks for the quick response. Works like a champ, Craig &q...

Querying Excel data without another program
Hello, I'm an intermediate user of Excel, but I have lots of experience using Access. If you can give me clues on how to handle the information below using only Excel, I'd be grateful! I have a spreadsheet in Excel 97 in which there's a Worksheet named, "Datasource" with a column "B" containing repetitive data. I'd like to create a new worksheet in the same Excel file which shows a single instance of each item used in Column "B". If I were writing the query in SQL, I'd say "SELECT DISTINCT [Column B] FROM [MySpreadsheet]![Datasource...

Excel Formulae #2
Hi, I have a spreadsheet with value in one column & a series of dates in 5 other columns.For each row depending on the value in the first colunm a date may be applicable in one or more of the other columns.e.g 550 in the first column will mean dates will be inserted in the next 2 columns similar to authority levels. My qn. is how can I show what's outstanding if the relevant authority levels have not signed off-basically this is used as a tracker of invoices & I would like to show what is outstanding awaiting approval at anytime. Thanks Raj ----------------------------------...

Add a Word Document as a Tab in an Excel Document
I work on several documents that require both worksheets and written reports - being able to add a word document as a new tab in Excel would be a neat way of integrating the two into one file for storing/printing/emailing. Maybe... http://www.pcmag.com/article2/0,4149,5224,00.asp "Office Binder: Gone but Not Really" PC Magazine article, January 29, 2002 by M. David Stone on using Binder in Office XP Jim Cone San Francisco, USA "GoDamN" <GoDamN@discussions.microsoft.com> wrote in message news:5F814119-2FA1-4BA6-92EB-C524C8C3820C@microsoft.com... > I work on s...

Email Help: Sending Outlook email from Excel VBA
Hi All, How do I select a specific property in outlook while sending email from excel? There is an option we can set in outlook outgoing emails call "Voting buttons" in that feature there is a custom option that we can select called "Have replies sent to" its a checkbox. I want it checked when i send an email from excel. Hope i made it clear. Thanks in advance If sending through the Outlook Object Model, use the MailItem.VotingOptions property and MailItem.ReplyRecipients collection. -- Dmitry Streblechenko (MVP) http://www.dimastr.com/ OutlookSp...

running excel in background
I'd like to run a program that writes values from one cell to another with a VBA program that is triggered by the clock. I've completed this part but, since I use active cells, you can't run another workbook without the clock activating the cell in the active worksheet. It needs to run in the background all day long. - ideas? will it run in the background if I don't activate cells? Try launching another instance of Excel. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Don" <thedonallen@yahoo.com&g...

how do why update my Excel microsoft office
how do why update my Excel microsoft office If you have office 5, you probably would want to update -- Don Guillett SalesAid Software donaldb@281.com "sam" <sam@discussions.microsoft.com> wrote in message news:2D951DBF-43DE-4C65-8CAC-B4E73DC572FD@microsoft.com... > how do why update my Excel microsoft office ...

How do I export email addresses from excel to outlook?
I am trying to do a mail merge using email via outlook. I have 200+ addresses and I'd like to know how to import the addresses into the contacts section of outlook to do the merge from there. I've tried the help part of out look but it comes up saying that the excel file has no named ranges and that I should use excel to name the range of data to be imported. Any help much appreciated. Thanks Mark In outlook select file/import export/ import from another program or file/ and then follow the instructions from the wizard. Why do you want to do the mailmerge from Outlook? You can...

Excel data disappeared after getting message about compatibility M
I tried to save changes to a spreadsheet, and received the following message: "Compatibility Report for New Customer List.xls Run on 4/6/2010 19:52 The following features in this workbook are not supported by earlier versions of Excel. These features may be lost or degraded when you save this workbook in an earlier file format. Minor loss of fidelity Some cells or styles in this workbook contain formatting that is not supported by the selected file format. These formats will be converted to the closest format available." I clicked OK, because fidelity is not imp...

No Smart Tag help: just a blank "MS Excel Help" window
When I invoke "help on this error" on a Smart Tag drop-down, a blank "MS Excel Help" window appears with no content. The general help pane that appears via <F1> etc. is fine - it's just the Smart Tag help option that doesn't show anything, just a blank window. Any suggestions? Win XP Home SP2 Excel 2003 (11.6355.6360) SP1 ...

VLOOKUP help!
I have 2 worksheets with ID numbers in separate comlums . When I use vlookup it gives me a N/A value in return. I have formatted cells as "text" as "general" - tried all sorts, but it still does not return a value. This is a simple vlookup action and I am getting no-where. I simply want to find out if the ID on 1 list is also on the other list! Please always post your formula. Formatting makes no difference. Some data may look like numbers but are actually text. Formatting doesn't change that. You can find out with the ISNUMBER() function. If numbers are actual...

Help me! About CTreeCtrl CustomDraw.
Hi: I use CustomDraw to custom color of my CTreeCtrl. All color was setted as I wish except the backcolor of left position line that always is white. Please help me! These is my source codes about I custom my CTreeCtrl: void MyTreeCtrl::OnCustomDraw(NMHDR* pNMHDR, LRESULT* pResult) { m_clrTextBk = ::g_ShowSetting.iTextBKColor;//the back color of my text m_clrText = ::g_ShowSetting.iTextFontColor;//the color of my text m_clrBkgnd = ::g_ShowSetting.iTextBKColor; //the color of background LPNMLVCUSTOMDRAW lplvcd = (LPNMLVCUSTOMDRAW)pNMHDR; switch(lplvcd->nmcd.dwDrawStage) { ...

PST Nightmare
Hey there, This is the last resort for sanity. My PST which holds ALL of my emails is on an external HD, which worked fine up until last week, is over the limit. I did find this out the hard way and ran the SCNPST as well as teh PST2GB things and still nothing. What confuses me is that once i ran the PST2GB thing it told me to complete it with the SCNPST but it would not do it :( The latest and greatest trimming through PST2GB has brought the size down to 1.9GB and some change but still no cigar... Now outlook is giving me the error:"..is not compatible with this versio...