Update Chart Source Data

I used the "Record Macro" function to get the code shown below.  When
I recorded the macro, the value of A1 was 392.  But when I actually
ran the macro, the value of A1 was 393.  As you can see, the "Record
Macro" function "hard coded" the value of 392.

How can I make this work such that the SeriesCollection is updated
with the CURRENT value of A1?

    Sheets("Chart2").Select
    Range("A1").Select
    Selection.Copy
    Sheets("Chart1").Select
    ActiveChart.PlotArea.Select
    Application.CutCopyMode = False
    ActiveChart.SeriesCollection(1).XValues = "='Approval Index'!
R2C1:R392C1"
    ActiveChart.SeriesCollection(1).Values = "='Approval Index'!
R2C5:R392C5"
    ActiveChart.SeriesCollection(2).XValues = "='Approval Index'!
R2C1:R392C1"
    ActiveChart.SeriesCollection(2).Values = "='Approval Index'!
R2C7:R392C7"
    Sheets("Data Input").Select
    Range("A13").Select

Thanks for your help...
0
iamnu
12/27/2009 11:32:07 AM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
641 Views

Similar Articles

[PageSpeed] 58

You can make your Series update without code using a 'Dynamic Range Name'

Looks like your X/category values are in 'Approval Index'!A1:A#
Series1-Y values offset in col-E
Series2-Y values offset in Col-G
where # is the value in A1.

Define the following names,

myCat =OFFSET('Approval Index'!$A$1,1,0,'Approval Index'!$A$1-1)
mySeries1 =OFFSET(myCat,,4)
mySeries2 =OFFSET(myCat,,6)

If A1 is not on 'Approval Index' change the sheet name accordingly

Change your Series1 & 2 formulas to
=SERIES(,Book1.xls!myCat,Book1.xls!mySeries1,1)
=SERIES(,Book1.xls!myCat,Book1.xls!mySeries2,2)

Change the workbook name to suit

Regards,
Peter T


"iamnu" <iamnubw@gmail.com> wrote in message 
news:ec8b2a62-f929-490c-b223-83bb8a9079b3@z4g2000prh.googlegroups.com...
>I used the "Record Macro" function to get the code shown below.  When
> I recorded the macro, the value of A1 was 392.  But when I actually
> ran the macro, the value of A1 was 393.  As you can see, the "Record
> Macro" function "hard coded" the value of 392.
>
> How can I make this work such that the SeriesCollection is updated
> with the CURRENT value of A1?
>
>    Sheets("Chart2").Select
>    Range("A1").Select
>    Selection.Copy
>    Sheets("Chart1").Select
>    ActiveChart.PlotArea.Select
>    Application.CutCopyMode = False
>    ActiveChart.SeriesCollection(1).XValues = "='Approval Index'!
> R2C1:R392C1"
>    ActiveChart.SeriesCollection(1).Values = "='Approval Index'!
> R2C5:R392C5"
>    ActiveChart.SeriesCollection(2).XValues = "='Approval Index'!
> R2C1:R392C1"
>    ActiveChart.SeriesCollection(2).Values = "='Approval Index'!
> R2C7:R392C7"
>    Sheets("Data Input").Select
>    Range("A13").Select
>
> Thanks for your help... 


0
Peter
12/27/2009 1:14:29 PM
The better way to do this is to make your series using dynamic named ranges
insert>name>define>name it something like graphrng1> in the refers to box>
=offset($a$1,1,0,counta($a:$a),1)
In the series, instead of hardcoded type in
=workbooknamehere!graphrng1
Do the same for the other series
Now your chart will be sell adjusting without using cell a1. Or, if you 
REALLY need to use a1 then
=offset($a$1,1,0,$a$1,1)

      If desired, send your file to my address below. I will only look if:
      1. You send a copy of this message on an inserted sheet
      2. You give me the newsgroup and the subject line
      3. You send a clear explanation of what you want
      4. You send before/after examples and expected results.
-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"iamnu" <iamnubw@gmail.com> wrote in message 
news:ec8b2a62-f929-490c-b223-83bb8a9079b3@z4g2000prh.googlegroups.com...
>I used the "Record Macro" function to get the code shown below.  When
> I recorded the macro, the value of A1 was 392.  But when I actually
> ran the macro, the value of A1 was 393.  As you can see, the "Record
> Macro" function "hard coded" the value of 392.
>
> How can I make this work such that the SeriesCollection is updated
> with the CURRENT value of A1?
>
>    Sheets("Chart2").Select
>    Range("A1").Select
>    Selection.Copy
>    Sheets("Chart1").Select
>    ActiveChart.PlotArea.Select
>    Application.CutCopyMode = False
>    ActiveChart.SeriesCollection(1).XValues = "='Approval Index'!
> R2C1:R392C1"
>    ActiveChart.SeriesCollection(1).Values = "='Approval Index'!
> R2C5:R392C5"
>    ActiveChart.SeriesCollection(2).XValues = "='Approval Index'!
> R2C1:R392C1"
>    ActiveChart.SeriesCollection(2).Values = "='Approval Index'!
> R2C7:R392C7"
>    Sheets("Data Input").Select
>    Range("A13").Select
>
> Thanks for your help... 

0
Don
12/27/2009 1:20:03 PM
I like crete data ranges the using the Address properties to get the
R1C1 addresses.


with Sheets("Approval Index")
.LastRow = .Range("A" & Rows.count).end(xlup).row
Sheets("Chart1").Select
Set ChartLabels = .Range("A2:A" & LastRow)
ChartLabelsAddr = ChartLabels.address( _
External:=true,ReferenceStyle:=xlR1C1)

Set Series1 = .Range("E2:E" & LastRow)
Series1Addr =
Series1.address(External:=true,ReferenceStyle:=xlR1C1)

Set Series2 = .Range("G2:G" & LastRow)
Series2Addr =
Series1.address(External:=true,ReferenceStyle:=xlR1C1)
end with

set MyChart = Sheets("Chart1")
Mychart.Select 
ActiveChart.ChartArea.Select

with ActiveChart
.SeriesCollection(1).XValues = "=" & ChartLabelsAddr 
.SeriesCollection(1).Values = "=' & Series1Addr
.SeriesCollection(2).XValues = "=" & ChartLabelsAddr 
.SeriesCollection(2).Values = "=' & Series2Addr
end with


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=165255

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

0
joel
12/27/2009 1:27:13 PM
Typo

> Looks like your X/category values are in 'Approval Index'!A1:A#
should be
  Looks like your X/category values are in 'Approval Index'!A2:A#

Peter T

"Peter T" <peter_t@discussions> wrote in message 
news:%23VYdCavhKHA.5380@TK2MSFTNGP06.phx.gbl...
> You can make your Series update without code using a 'Dynamic Range Name'
>
> Looks like your X/category values are in 'Approval Index'!A1:A#
> Series1-Y values offset in col-E
> Series2-Y values offset in Col-G
> where # is the value in A1.
>
> Define the following names,
>
> myCat =OFFSET('Approval Index'!$A$1,1,0,'Approval Index'!$A$1-1)
> mySeries1 =OFFSET(myCat,,4)
> mySeries2 =OFFSET(myCat,,6)
>
> If A1 is not on 'Approval Index' change the sheet name accordingly
>
> Change your Series1 & 2 formulas to
> =SERIES(,Book1.xls!myCat,Book1.xls!mySeries1,1)
> =SERIES(,Book1.xls!myCat,Book1.xls!mySeries2,2)
>
> Change the workbook name to suit
>
> Regards,
> Peter T
>
>
> "iamnu" <iamnubw@gmail.com> wrote in message 
> news:ec8b2a62-f929-490c-b223-83bb8a9079b3@z4g2000prh.googlegroups.com...
>>I used the "Record Macro" function to get the code shown below.  When
>> I recorded the macro, the value of A1 was 392.  But when I actually
>> ran the macro, the value of A1 was 393.  As you can see, the "Record
>> Macro" function "hard coded" the value of 392.
>>
>> How can I make this work such that the SeriesCollection is updated
>> with the CURRENT value of A1?
>>
>>    Sheets("Chart2").Select
>>    Range("A1").Select
>>    Selection.Copy
>>    Sheets("Chart1").Select
>>    ActiveChart.PlotArea.Select
>>    Application.CutCopyMode = False
>>    ActiveChart.SeriesCollection(1).XValues = "='Approval Index'!
>> R2C1:R392C1"
>>    ActiveChart.SeriesCollection(1).Values = "='Approval Index'!
>> R2C5:R392C5"
>>    ActiveChart.SeriesCollection(2).XValues = "='Approval Index'!
>> R2C1:R392C1"
>>    ActiveChart.SeriesCollection(2).Values = "='Approval Index'!
>> R2C7:R392C7"
>>    Sheets("Data Input").Select
>>    Range("A13").Select
>>
>> Thanks for your help...
>
> 


0
Peter
12/27/2009 1:52:12 PM
Reply:

Similar Artilces:

Finding second data point on primary axis of two axis chart
Hi I am new to Excel 2007 (moved from Excel 2003). I have a chart with one column on the primary axis and a line on the secondarry axis. I added a second series of data on the primary axis and can't find it. This usued to happend with my previous edition of Excel and was quickly remedied by putting the cursor on the horizontal line until I found it. Is there another way to find that second series of data? Thanks in advance. Al Charbonneau Al Charbonneau _ Select a data series. Use the up-down arrow keys to cycle through the major chart objects until the a...

how to create row with data
imagine i have two rows: name and color: mary blue ken brown will blue what i want is having, in another row, just the names that have blue eyes, like mary will thank you On Mon, 11 May 2009 17:26:04 -0700, rafael <rafael@discussions.microsoft.com> wrote: >imagine i have two rows: name and color: >mary blue >ken brown >will blue > >what i want is having, in another row, just the names that have blue eyes, >like >mary >will Like: mary will or like: mary will ???? ty i have excel 2000 what i want is all names in a single row, wi...

Windows Update for XP Pro 64bit
I can't get updates from Microsoft for my 64bit XP. Even though I am logged on to the computer as administrator, the site says I have to be logged on as administrator. I have checked user accounts on the machine, and tried right-clicking on Microsoft Update, run as administrator. It still won't update! Anyone have an idea on how to fix this? -- jmsvbit Do you have SP3 installed . Maybe because of that reason -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "jmsvbit"...

data import and word templates missing from CRM
Hi. I'm working with a new install of CRM and there are some icons missing in the setttings area such as the import function on the data management page and word templates under templates. Any ideas about why this is? Thanks. ...

Debit or Credit notes not updating to GL
Hi, I have problem in GP 10 SP4, accidently removed the link in Tools>Setup>Posting>Posting> :Series:Series Origin: Receivables Sales Entry post to GL not checked, I have checked and tested 1 trsancation and its updating in GL. I have some old trsansaction which not updated in GL, How can i make this whole data to GL, instead of entring one by one. Rgs Saf Hi Saf, I am not aware of any option and I believe there none. "saifu" wrote: > Hi, > > I have problem in GP 10 SP4, accidently removed the link in > Tools>Setup>...

Updating share price with many decimal places in investments
Hi I have MS Money 99 (in Portuguese) and I'm trying to control my investments in funds with MS Money. I've created an account for the investment. As this investment is a fund investment, the share price has many decimal places (e.g 3.045689). When I try to update de price of this share on any given date, it rounds the value (e.g 3.000) putin 3 zeros after the decimal key. How do I handle with this situation ? Many thanks, Marcio. ...

Select Query acting like an Update Query
I am having trouble with my database where my select queries are acting as update queries. If a user happens to accidentally or otherwise adjust the results provided by the query, it is updating the information on the table(s). I need to find out why it is doing this and how to prevent this from occurring and altering the data. Thank you, Terri On Dec 17, 9:34 am, Tboartz <Tboa...@discussions.microsoft.com> wrote: > I am having trouble with my database where my select queries are acting as > update queries. If a user happens to accidentally or otherwise adjust the > res...

Question with local vs. remote (Exchange server) data
Can someone please explain something basic to me about the way Outlook works. I'm a Mac user and not familiar with this thing but now need to figure out how to synchronize it with a PocketPC PDA device. We've got Outlook on our PCs (W2K), connected to an Exchange server. So, I see my calendar and contacts, which I know live on the server. But when I sync my device, it ends up empty. Is there some sort of "local" (to my PC) data that it's using instead of the Exchange server's info? If so, where is it and how do I find it? If I could find it, I could copy the Exchan...

Bytes in Data Page Row
I am reading a book titled "Microsoft SQL Server 2008 Internals". In Chapter 6, which was authored by Kalen Delaney, Kimberly Tripp, and Paul Randall, the following table and Clustered Index is created: CREATE TABLE Employee( EmployeeID INT NOT NULL IDENTITY, LastName NCHAR(30) NOT NULL, FirstName NCHAR(29) NOT NULL, MiddleInitial NCHAR(1) NULL, SSN CHAR(11) NOT NULL, OtherColumns CHAR(258) NOT NULL DEFAULT 'Junk') GO ALTER TABLE Employee ADD CONSTRAINT EmployeePK PRIMARY KEY CLUSTERED (EmployeeID) GO It states on page 321 of the aforementione...

Conditional Formatting
I have a row of cells that calculate the totals for their columns but I force to be blank via IF statements until certain conditions are met. Once these conditions are met, the total appears. I'd like to also highlight these total cells via conditional formatting. My conditional formatting input is simply "If Cell is Greater than 0, format". For some reason all the cells format as though they are all > 0 yet they don't display anything. Are there some internal rules that specifiy when a cell is really 0? Is there another way to do this that won't care what the for...

Analysing data from several excel workbooks
Hi, I'm totally new to excel and i need to analyse the changes in a funding position across several years in a seperate excel sheet. As in, submissions are made year on year showing the amount of revenue spent on several different services. I need to be able to compare how those have moved over the last 5 years in a single spreadsheet. I appreciate any help. Thanks To give a clear answer we would need a bit more detail of the data layout Give us a simplified version of what the data looks like Your 'subject' talks about different workBOOKS but the text of the ...

UPDATE UNAVAILABLE
I've been updating my US Bank and WAMU checkiing and savings accounts for over 2 months now. Just last week, I started getting an error message: "Update Unavailable." When I click to get more information, I get a message saying that my banks no longer support synching with Money!!!! Has anyone had this experience? In microsoft.public.money, Microsoft Money <Microsoft Money@discussions.microsoft.com> wrote: >I've been updating my US Bank and WAMU checkiing and savings accounts for >over 2 months now. Just last week, I started getting an error message: ...

Managing Data Table
Hi, I need help to wrap the tex in Data Table. If any one knows how to do it, please let me know. Thanks for your help in advance. Yogesh Hi Yogesh What version of PowerPoint are you using? What kind of table is it and how did it get there (e.g. insert -> table, or a table connected with a graph)? If it's a graph, is it an Excel graph or an MSGraph graph? Lucy -- Lucy Thomson PowerPoint MVP MOS Master Instructor www.aneasiertomorrow.com.au "Yogesh" <Yogesh@discussions.microsoft.com> wrote in message news:F68FDA89-6289-4341-8886-54C580...

recovering data
Money 2000: Is there a way to recover data that was not backed up. I accidently deleted all deposits. When I used the restore file procedure all saved deposits were restored, but all deposits and withdrawals that hadn't been backed up were deleted. How can I get that work back? Thanks! Retype it all? I mean, seriously, if you type three pages to a document in Word that you last backed up a month ago, delete the three pages and then quit Word where are you? "ETHORN" <anonymous@discussions.microsoft.com> wrote in message news:e40d01c3f101$8989da00$a101280a@phx.gbl...

Importing data #5
Hello, I have a workbook in Excel 2003 that I need some advice on please. The data used in the spreadsheet is collected online into a CSV file, this CSV file is imported into Excel onto a "Raw Data" worksheet. From this raw data several other worksheets need to access this raw data to prepare the reports required. I have the first report sheet designed and it picks up the data from the Raw Data sheet. The problem I need help with is updating the raw data; when I download a new file from our web site and refresh the data import the report worksheet does not update correctly. Fo...

Append Query
I have a table "Rates" with the following data: StartDate: 5/15/08 End Date: 6/23/08 Rate: $53 StartDate: 6/24/08 End Date: 7/15/08 Rate: $86 StartDate: 7/16/08 EndDate: 9/19/08 Rate: $99 I have another table "Transactions". I want to create an append query such that if I supply the StartDate and EndDate, the query will populate the "Transactons" table with date and rate data from the "Rates" table for each and every day within the date range. Example: If I supply the StartDate of 6/23/08 and the EndDate of 6/25/08, the query would pop...

crash word & update
The context : word on Ibook OS 10.2.6. Since two weeks, Word crashes=20 anytime I want to save a file I'm working on. First thing I tried : = clean=20 the disk and the library by means of Onyx. Nothing has changed, so I=20 tried to upgrade the Office package, but the upgrade doesn't work :=20 error 11002 : 2, -15. Does anyone know how can I solve these problems? thanks a lot fred Remove and re-install Office X. Insert the Office X CD, then open the Value Pack. Click the Value Pack Installer. Check "Remove Office", then click "Continue". Run the "Remove Offi...

How to combine/consolidate data
Hi, I am about to create an Excel file that should be used by custmers to place orders. On the first sheet, I would like to have a summary of the entire order, so the customer can see what he ordered. All data is organized in same sort of tables accross several sheets - each sheet representing different product group. So, lets say I have table organized like below: ColumnA ColumnB ColumnC Product Price Order Prod1 10 1 Prod2 20 Prod3 20 2 And I have several of such tables on different sheets corresponding to each prodcut category. Now, on the first sheet, I w...

Saving chart as an image file
I want to create a pie chart in Microsoft Excel, then save it as an image file (.jpg or .gif) so I can put it on a website. How do I do this? Secondly, as I create my pie chart, how do I set my data labels? I want to change them from the default (numbers) to my own description for each slice. Jon Peltier has some instructions in his Excel Charting FAQ article for exporting a chart in gif format: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=209#jon008 To add your own labels to the chart, you can use Rob Bovey's XY Chart Labeler. It's a free add-in that yo...

Traspose Column data into Row
Hi, I want to transpose column data in to row. I have seen many posts (using INDEX or OFFSET functions) but all these solution assume a fixed block of data to be transposed, My problem is that the data i have not only has variable blocks to be transposed but also has some duplicate headings(headings are duplicate but the data in front of each heading has different value. Below is the example of data Col A Col B Col C PRODUCT COST COMPONENT COST$ A Raw Mat 10 A D L ...

Macro to create a new chart
I've been using this code for a while to easily create a new chart. However just recently has stopped working. It now created a "default" excel chart as a new tab in the workbook. Any suggestions? '******************************************************************** 'Creates 2 data series horizontal bar chart '******************************************************************** Dim myShtName As String myShtName = ActiveSheet.Name Charts.Add ActiveChart.ChartType = xlBarClustered 'Creates Stacked bar ActiveChart.SetSourceData Source:=Rng, ...

Pivot chart: get rid of zeroes?
Hi, I have created a pivot chart using a pivot table. However I want the zeroes to be NULLs so they are displayed on the graph. Unfortunately I cannot see how to do this; could someone please help? I have tried the Table Option checkboxes in the bottom right corner to no avail. Thanks Sorry, that should read NOT displayed on graphs "Phil Davy" wrote: > Hi, > > I have created a pivot chart using a pivot table. > > However I want the zeroes to be NULLs so they are displayed on the graph. > > Unfortunately I cannot see how to do this; could someone p...

SQL Query to transform/group data by Date
Hi, I have a large Access table with data organised as follows: Field1: Code Field2: Date Field3: Value1 Field4: Value2 There are seveal different codes and therefore duplicate dates. I'd like to run a query to bring back each code grouped by date and so put the codes along the top as feilds. For example the query below brings back the following data for two codes. SELECT field2, field1, field3 FROM Data WHERE field1 In ('LLOY','RSA'); 21/01/05, LLOY, 12454 22/01/05, LLOY, 31541 21/01/05, RSA, 21241 22/01/05, RSA, 12414 Instead I want the data to look like this: ...

form: data input locking up
Hello all... I have a form which was working fine until a few hours ago. The form has several sub forms. However, those are working fine. I am unable to edit/add/delete records on the main form. This is a front end database, with the main data storage on a network. I moved the data starage DB onto my local drive, reset the links and experienced the same problem. The form is not based on a query and writes directly into a data table. The form is normally called up (filtered) through code. however, I experience the same problem when I open it up directly (without filtering the re...

Google Search Add-in update
Hi all If you use this add-in then update it to version 6.01 Reason : The product specific Knowledge Base search is not working in version 6.0 anymore because Microsoft changed there Knowledge Base search. I update the Excel add-in to version 6.01 to fix this. http://www.rondebruin.nl/Google.htm -- Regards Ron de Bruin http://www.rondebruin.nl Thanks Ron. Will go get and implement. -- Don Guillett SalesAid Software donaldb@281.com "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:unUTwWnzEHA.3376@TK2MSFTNGP12.phx.gbl... > Hi all > > If you use t...