Chart With Changing Source Data

Aug 11, 2006

I am trying to create a macro which will allow me to change the source data for a particular chart. what i want to do is to have a code which looks in a range of cells (A8:B28) and then updates the chart to only include those cells within the range that contain a value? The number of cells with values changes depending on what options are selected elsewhere in the spreadsheet.

View 2 Replies


ADVERTISEMENT

Changing Chart Source Data Range

Nov 30, 2006

Each week I have to add a new column to a report and then update the source data of all the charts that use that newly added column. I am trying to create a macro that will use the last 12 columns of data located on a different worksheet as the range for the source data of the chart. I have attached an excel document that contains an example of how one of the charts that I am using looks and where it pulls the data from.

View 3 Replies View Related

Changing Chart Source Data Based On Running Date

Apr 4, 2007

I currently have a simply bar chart that is based on a running 5 days figure so the X-axis is like 01-Mar 02-Mar 03-Mar ...

Y-Axis is the data that I am trying to present - Total $

The source of this data is simply two columns - Column A is the running date, Column B will be data.

Daily I need to only present 5 days historical data based on the latest date i.e. today and the past four days (the source data is updated daily).

Can a macro be used to auto update the bar charts based on today's date?

View 9 Replies View Related

Changing Chart Source With Vba

Jan 31, 2007

I'm trying to create a chart with different source ranges e.g if i used

With ch.Chart
.SeriesCollection.Add _
Source:=Worksheets(1).Source:=Worksheets(1).Range("C1:C8,J1:J8")
End With

That works fine but I want to modify the columns it looks at. I was going to use the cells(x,y) method but I can't get it to work. icol = 3. Range(Cells(1, iCol), Cells(8, iCol)). This works for a single range(C1:C8), but how do i reference Range("C1:C8,J1:J8") in the same way.

View 3 Replies View Related

Changing A Chart Source Sheet

Feb 9, 2007

I have a sheet ( Graphs A) with 50+ graphs on all linked to a datasheet (Data A).

I now want to copy these graphs to another sheet (Graphs B) which links to a different data sheet (Data B). The data is in the same layout format etc.

The problem is that the find-replace function will not replace the sheet name in the charts series formula from Data A to Data B

Is there an easy way to change the source sheet for the new graphs?

View 3 Replies View Related

Changing Labelcolor In Chart Based On Source Text Color

Mar 5, 2013

I have data. 2 columns a2:b5

each row has a different font color

apple 4
banana 3
peach 2
pear 1

I want the label in a chart to march the font color of the source cell

So in a chart I'd have 4 labels:
"apple"
"banana"
"peach"
"pear"

I can't see how to do it. When i select the albels it's "all or none". I can change ll the labels to a color. But, I want each individual label to be a diferent color.

View 3 Replies View Related

Changing Formula Data Source

Oct 16, 2006

I've a set of excel sheets reading from a single excel data source, the only problem is that the data source is constantly changing, for example:

=DCOUNT('T:GreenbeltProductivity ToolWeek 42[Dept Overall and Data Entry.xls]Insert Data - Current Position'!A:F,5,AC139:AD140)

The week number is the only bit of the code that needs to change week on week, to update the data source on all the sheets and I'd like to password protect the data source.

View 5 Replies View Related

Can't Reconnect Slicers After Changing Data Source

Feb 11, 2014

I have a workbook with some pivot tables connected to a single slicer. My macro needs to disconnect the pivots from the slicer, change the data source, and reconnect the slicer. When my code gets to reconnecting the slicer it bugs out as if the data source is not the same for all the pivot tables (also doesn't let me reconnect manually). But when I look at the source, it is all the same and correct. If I manually set the data source the slicer reconnects just fine. Why is my code causing problems with the slicer?

Here is my code for setting the data source. Pretty sure this is the code that is causing problems for me.

Code:
newaddress = "$B:$" & Mid(Cells(1, YTDcolumn).Address(), 2, 2)
.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:="TOP250Excel!" & newaddress, _
Version:=xlPivotTableVersion14)

Here is my code for reconnecting the slicer connections.

Code:
ActiveWorkbook.SlicerCaches("Slicer_Top250Excel_cypheren_Query_Name"). _
PivotTables.AddPivotTable (.PivotTables("PivotTable1"))

View 1 Replies View Related

Changing Source Data Of An Existing PivotTable

Jun 2, 2008

I'm trying to change the source data of an existing PivotTable so that it is linked to a raw data source in another workbook. The workbook it pulls the data from changes every month so I made this flexible code that is meant to pull the raw data every month:

Sub ImportNewSource()
Dim Filt As String
Dim FilterIndex As Integer
Dim Title As String
Dim FilePath As Variant
Dim ThisPivot As PivotTable
Dim FileName As String
Dim ShtNum As Integer
Dim LastRow As Long

The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.

When I do it manually, it works just fine. There aren't any changes to the PivotTable field or anything like that so I'm stumped. I've tested the range that has the source data and its selecting the proper range and when I do it manually it works fine.

View 9 Replies View Related

Set Source Data For Chart Using R1C1

Mar 9, 2014

Everything else in my macro is using R1C1 format for ease of automation. However, I cannot get this to resolve. I am setting the source data for a line chart.

However, if I use A1 format it works just fine.

I am using R1C1 for automation purposes. A1 format will not easily work. I have the interpolated statement in there because the range contains some empty cells. I want excel to fill the line in the chart using interpolation. All the data in the columns(N through R or 14 through 18 in R1C1 format) is contiguous. I have not tried Union, but it seems I should just use this range like I do in the A1 format.

View 6 Replies View Related

Manipulating Chart Source Data With VBA

Aug 8, 2007

I have a chart that I am trying to set up with dynamic data. I have the chart created, but the problem is that I will have a variable number of categories based on the data that is loaded, so I need to use VBA to change the range for the source data and labels on the fly (otherwise, I will end up with 20-50 categories that are all zeroes, putting a bunch of gobbledegook at the top of the chart).

What commands to I need to enter into VBA to get this to work?

I have put a formula in 2 cells to determine based on the current data what the source data values range should be, and what the category labels range should be.

The cell that holds the value range is Graphics!L6, and the cell that holds the labels range is Graphics!L7.

The current values that these cells hold are:

L6:
=Graphics!$J6:$J9
L7:
=Graphics!$K6:$K9
Oh, and the name of the chart is "Chart 1"

What do I need to put into VBA to get the chart to change the data source values and category labels to the ranges I have listed in those 2 cells? (and for that matter, have I listed the ranges correctly in those 2 cells so that they are usable?)

If possible, I would prefer to do this without having to use code to select the chart, because I want the updates to the chart to be invisible to the user.

View 9 Replies View Related

Chart Not Reflecting Data Source

Jun 29, 2006

In my table I have rounded my values to a particular number of decimal places. BUT, the bars of my chart have the length of the original numbers. This is especially ridiculous when in the table there are only (rounded) zeros, and in the chart I get several bars with different length. how to plot the bars in the same length as the numbers in the table are?

View 9 Replies View Related

Chart: Selectable (ddl) Data Source

Aug 10, 2006

I have a sheet with logged test data.

Various test items are stored in columns.
(First row of each column is the description of the test item)

Problem:
1) To reflect certain testdata in a chart I want to be able to select a different column (Y values) by means of a dropdownlist.
X values are just the number of items (1 to ...)

How to make the charts data source selectable?

2) It seems that the input range of a ddl should be a list of items.

In my case they are in the first row of each column so only the first item is shown.

View 5 Replies View Related

Dynamic Chart Source Data

Jul 27, 2007

In a workbook that I am working on, a macro runs to import data onto 9 separate sheets. The data is essentially x- and y- coordinates of a plot profile from another program. The problem that I am running into is that the number of data points can vary each time the data in imported based upon how the data was acquired.

If I were to put a drop-down list (Method1, Method2, Method3, etc.) on the Instructions page that I have for this workbook and have the user select which method was used prior to running the macro, could I then incorporate some sort of IF statement in the macro to vary the Source Data Series for the charts that I have created? Right now, I have the Source Data covering the scenario with the longest list of data points so when fewer points are used, the graph looks essentially useless.

View 5 Replies View Related

Change Chart Source Data From Add-in

Aug 9, 2007

I have an excel addin that contains chart templates. Without going into too much detail a user will select an option which will update these templates then move the charts along with the source data to a new workbook. My question is this; How do I change the chart references programatically so that the chart data now points to the source data in the new workbook.

View 5 Replies View Related

Color Chart Data Points By Cell Values Of Non-Source Data

Apr 14, 2008

I have a bar chart that was made from table 1 which has the value in it. I have another table, table 2 that has the name corresponding to the value in table 1. I want to color data points in a bar chart with a different color for each name I have in table 2. I also want to give a data label in the data points with the value I have in table 2 instead of the original data label from table 1. Is it possible to do it?

View 3 Replies View Related

Enable Editing Chart But Not Source Data?

Mar 18, 2014

is it possible to unlock a chart but then disabling to change the source data?

Background: I want the user to be able to add numbers etc. but he shouldn't see the underlying table...

View 2 Replies View Related

Chart Source Data: Union Of Every N Cell

Sep 19, 2006

I have 4 sheets. In each sheet, I have a column of data for every hour of every day of one year.

In the 1st worksheet, I want to make a chart using :

- for Y values : the average of the data for each day
- for X values: the day

In the 3 other worksheets,
- for Y values : the data for each hour
- for X values: the hour

To make it more simple, I will first build in the "D" column the data of the average data for each day (so, every 24 columns)

Here is my

Dim lNbSheet As Long ' sheet number
Dim rSheetData As Range ' what will be my Y values
Dim rSheetTime As Range ' what will be my X values
Dim sSheetName As String ' current sheet

For lNbSheet = 1 To 4

'selection of the Y values
Sheets(lNbSheet).Select
sSheetName = ActiveSheet.Name

I get the error "methode SeriesCollectiosn of Object _chart failed;

Also, if you have an idea that could make me avoid writing all the new average-for-the-day data in column D (using it directly),

View 8 Replies View Related

Graph & Chart: Data Source Changes Monthly

Nov 19, 2006

I am trying to write a macro that will automatically change the source data for a graph. This is an existing graph that is on tab GraphYTD and the data for the graph comes from DataYTD. The number of rows of data will change monthly, but I would like to use the same graph template every month. Here is my

Sub SortYTD()
Dim myBottom As Long
myBottom = Sheets("DataYTD"). Range("B65536").End(xlUp).Row
ActiveChart.SetSourceData Source:=Sheets("DataYTD").Range("F2:I" & myBottom)
ActiveChart.Location Where:=xlLocationAsObject, Name:="GraphYTD"

End Sub

I am getting Runtime error 91 - object variable or with block variable not set.

View 3 Replies View Related

Link Spinbutton To Chart Data Source

Nov 21, 2006

For example, if my graph currently shows B1:L1 range, how by clicking a spin button to make the graph show B2:L2 range?

...

View 9 Replies View Related

Code To Change Chart Source Data

Jul 16, 2007

I am in the middle of building a macro for some data to be automated. I have two sheets, one of which has an existing chart but the data has been removed so now there is no values on the chart. I then have a second sheet with data on it. The range is b4:c16. I want to point this data onto my chart on the other sheet.

View 2 Replies View Related

VBA - Change Data Source Of Pivot Table With Range Changing Based On Cell Value?

Mar 5, 2013

I have the following code to update a pivot table:

Code:
Dim pt As PivotTable
Application.EnableCancelKey = xlDisabled
For Each pt In ActiveWorkbook.Worksheets("sheet1").PivotTables
pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:="source!R4C1:R33443C55" _
, Version:=xlPivotTableVersion10)

Next pt

the R33443 term is what will be changing, the columns and the starting row should stay the same. is there a way to instead of using R33443, to enable the range to be changed based on a cell value?

View 2 Replies View Related

VBA - Retrieve Chart Series Source Data In Range?

Jan 13, 2010

Currently I'm working in an excel file that contains a lot of charts. I want to write a macro that changes the source data of every serie in every chart. It only needs to add extra rows. But as every series has different columns where they get the data from, how can I change the rows without changing the columns?

This is what I've written so far:

Code:
Sub ammend_datarange_series_all_charts()
Dim start_cell As String, end_cell As String
Dim chart_tot As Integer, c As Integer, s As Integer

[Code]....

As you can see I've tried two things, first I tried to change the string of the source data, however this meant that every series would have C and E as their source data. So I wanted to retrieve the x and y values in string format. This does not work yet, it returns an array with the values of all the entries in the series source data. From that array I can not retrieve the column where the data came from.

View 4 Replies View Related

Combination Chart: Present Source Data Differently

Sep 27, 2006

I've taken over a spreadsheet and been asked to produce various charts. One chart in particular asks for views of the following (hyperthetical) data from different workbooks on the same sheet

July 06 - 24%
June 06 - 22%
May 06 - 29%
April 06 - 21%

overlaid with this data

April 06 - 42
May 06 - 68
June 06 - 47
July 06 - 55

The problem I'm having is that firstly, the dates are presented the opposite way round, and secondly one set of data is in percentages, the other in basic integers. The spreadsheet data is large, and historically has always been done like this so it's not easy to change the way its presented, but is there an easy way to show it in a combination chart?

View 6 Replies View Related

Copy Chart Without Source Data & Retain Formatting

Feb 21, 2008

I have 2 sheets in one workbook; one sheet has a chart with a data table, the other contains the source data for the chart. I want to copy and move the sheet with the chart to a new workbook, but without the source data. I break all links, but then the chart's data table takes the default values and formats in the new workbook after I break the links. For examples, the source data has formats such as currency and no decimal places. However, after copying the sheet, the data table reverts to no dollar sign and decimal places. In addition, the months on the x-axis turn to numbers when the links are broken.

View 3 Replies View Related

Dynamic Named Range As Chart Source Data

Jun 16, 2008

I've read through every thread I can find about dynamic named ranges as source data for a chart. It seems as if everyone can get it to work but me. I have a chart on one sheet that needs to update dynamically from data on another sheet when a button is clicked. Here's my code that runs when I click the button:

Sub Update_Center_Chart()
Sheets("Center Data Chart").Select
ActiveSheet.ChartObjects("Center Data").Activate
ActiveChart.SetSourceData Source = Range("CenterData")
End Sub

I get the error message: "Type mismatch." The data is in B1:F2 on a different sheet. The formula in the named range, "CenterData" is this:=OFFSET('Center Data'!$B$1,0,0,COUNT('Center Data'!$B:$B),4). The data will expand by 1 row every time.

View 5 Replies View Related

How To Prevent Pivot Tables From Changing Source Data Numbers Into Dates In Field Columns

May 13, 2013

why is pivot table changing numbers to dates. It was fine all along, numbers retained the fromat of source data and now, all numbers in field columns are turning into dates. is there a setting I can change to prevent excel from reformatting numbers to dates?

View 1 Replies View Related

Error Message When Editing XY Chart Dynamic Source Data?

Feb 9, 2012

I am trying to create a simple xy graph in Excel 2010 using dynamic source data.

I have data in three columns.

There are headings on line 2 and the data starts on line 3.

A B C
1.
2. dp ss ff
3 .1 .2 .3
4 .3 .4 .4
5 .4 .8 .7
6 .1 .6 .8
7 .3 .5 .1
8 .5 .4 .4

I want to create an XY chart with C3 to C8 as the X axis (or dynamic to what ever the last data point is) with A3 to A8 as the Y axis (or dynamic to what ever the last data point is).

I have Defined names for C3 to C8 (myXvalues) and A3 to A8 (myYvalues).

I have created a simple XY graph and selected the ranges as normal.

This is =DATA(,Data!$C$3:$C$9,Data!$A$3:$A$9,1)

The page is named DATA

What I am trying to do now is to edit this range to make the range dynamic.Unfortunately I get error messages which ever way I try to edit the range.

I have tried:-

=SERIES('Data'!$c$3,'Data'!myXvalues,'Data'!$A$3,'Data'!myYvalues,1)
=SERIES('Data','Data'!myXvalues, 'Data'!myYvalues,1)
=SERIES(,Data!myXvalues,Data!$myYvalues,1)
=SERIES(Data!,Data!myXvalues,Data!myYvalues,1)

The data range can go from 8 to 30000 hence the need for a dynamic range!

PS: Also, is there any way to create this graph and dynamic source data in VBA. I have tried this with no success.

View 2 Replies View Related

Sorting Chart Legend To Match Source Data Order

Feb 23, 2012

The order of entries in my chart legend make no sense to me. Why is it not matching the order of the source data?

View 1 Replies View Related

Copy Chart To New Sheet & Have Charts Source Data Reference It

Jan 24, 2008

Sheet1 contains a chart using a dynamic name in Source Data: Values. The dynamic name data is controlled by another cell. When I copy the sheet - i.e. Sheet1 (2) - and change the control cell, the Source Data: Values in the copied sheet still refers to the original Sheet1.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved