Automatically Adjust Dynamic Chart As Data Added To Source
Jun 19, 2008
Is there a way to make a chart adjust automatically when you add to the source data? I'm trying to use a named range in order to power a chart (something like chart_data=offset(a1,,, counta(a:a),counta(1:1)); the range is working fine). When I put "chart_data" into the source field for the chart, it seems to work fine; it picks up the right range... But when I later add more data, the chart source data field seems to have converted my named range into a hard-coded range, absolute range. So the dynamical named range updates, but the chart doesn't and I don't see the new data. Am I doing something wrong that I need to tweak? Does what I'm trying to do just not work? Is there another way to make the chart pick up the new data without adjusting the range?
View 3 Replies
ADVERTISEMENT
Oct 17, 2013
How or if it is even possible to have a table automatically adjust its range or the number or rows it has based on another table
I have two tables:
Table1 is raw data that is added two daily
Table2 has formulas with column header references to Table1 that processes the information and converts it to a readable format. (basically barcodes to words)
Is there a way to get Table2 to automatically resize to the same number of rows that Table1 has whenever data is added??
View 2 Replies
View Related
Mar 3, 2013
i am trying to build a spread sheet showing materials in a stores tools, materials,and usage ect. i have put all the formulas in for example cell c =sum(b2-a2) to get a remaining stock left after usage but what i need to know is a way to add more new stock onto the sheet because every time i go to add on new stock it just resets itself is their anyway of doing it so that when you add new stock to cell a for example it automatically adds on to the total in cell c??
View 1 Replies
View Related
Mar 5, 2013
I have a worksheet used for a car game. Each row is designated to show one particular car that can be used in the game, so let's say that there are 40 cars taking up 40 rows.
Column A shows the car type and model; column B shows the engine that the cars has represented by a number (the higher the number the better the engine is); and so it continues, Column C shows the transmission; column D the brakes etc.
Underneath the 40 rows (lets say from row 45 to 65) I then have a table with spare-parts that can be used to replace the cars existing parts. This Parts-table is also arranged with engines in column B, transmissions in column C, brakes in column D etc...
This point of this layout is that it should be easy to compare each type of part in the spare-parts table with the corresponding type of part already used in any of the cars as everything is perfectly lined up by the columns.
About the spare-parts table: because the inventory in the spare-parts table often are added to or subtracted from, as parts are won in races or being put on the cars, I have created a macro to sort the parts in each column so as to put the best parts at the top of the table and the bad ones towards the bottom - so far so good.
The problem is this - as I add new cars, the table of spare-parts is pushed down worksheet and that means that the macro no longer will reference the right rows. So the question is this, how do I automatically update the macro to adjust for rows being added above it? I am not really interested in redesigning the worksheet.
View 2 Replies
View Related
Aug 19, 2006
I have attached a copy of the chart and data. My chart starts on the bottom. I had to take out a lot of data but beneath the data shown is much more data..
I want my chart to automatically update when new data is added. Here are the issues. I have several lines of headers and below that my data. Then below my data I have other data that I don't want displayed in the chart (its not shown here because of size limitation of attachmnet).
The new row will be added each time right below the header, right above the
most recent data given. So a row will be added above row 8!
View 9 Replies
View Related
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
Mar 31, 2008
I have various charts others view and require the data range to change in 12 month intervals, ie Jan07 -Jan08, Feb07-Feb08. I currently move the ranges manually every month but find the it would be more interactive for others if a drop down combo box on the chart were used enabling the user to choose which intervals to view.
View 5 Replies
View Related
Apr 28, 2009
I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.
View 4 Replies
View Related
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
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
Apr 17, 2014
I have a table of company data, and I want to be able to select different companies data and create a chart automatically perhaps by pressing a 'create' button.
The table data contains three values I want to populate in the chart: red & green sales which I would like shown as a stacked bar, and red as a percentage of total sales which I would like on a different axis as a line.
I tried to create a macro but my VBA skills are almost non-existent.
View 2 Replies
View Related
Apr 4, 2008
I'm using a template that has formulas using a $ sign to attempt to stop the ranges they refer to changing. The problem is, when I use the template (which involes Access importing some data and adding columns to the sheet the formula refers to in the process) the rows referred to in the formula change in line with the number of rows of data that have been imported. Only the row numbers change not the column headings. So for example:
=( SUMPRODUCT(((Workings!$H$3:$H$1000=Explanation!B9)+(Workings!$E$3:$E$1000="Buy")*(Workings!$J$3:$J$1000))))-998
becomes
=(SUMPRODUCT(((Workings!$H$511:$H$1508=Explanation!B10)+(Workings!$E$511:$E$1508="Buy")*(Workings!$J$511:$J$1508))))-998
View 3 Replies
View Related
Apr 22, 2013
A lot of the Workbooks that I design for use by myself and colleagues require data to be copied in from external data sources. To avoid named ranges from failing, I always use the following method:
Calculate the length of the data set:
Code:
=COUNTA(INDIRECT("'Data Sheet'!"&"$A:$A"))
(There will be no gaps in the data, hence a count is fine.) This named range is called DSROWCOUNT.
Example named range for the data in column A:
Code:
=OFFSET(INDIRECT("'Data Sheet'!"&"$A$1"),1,0,DSROWCOUNT-1,1)
I use INDIRECT to ensure my named ranges do not fail if the data is deleted (accidentally or intentionally), as #REF! errors will occur.
The problem with this method is that it automatically makes the Workbooks volatile because of the use of OFFSET and INDIRECT, hence the Workbooks always needs to be in manual calculation mode to be usable.
View 7 Replies
View Related
Jun 7, 2006
[please refer to attached sample data]
The formula in K3 is
"=J15/10"
This formula calculates the average mailing return rate of all the mailings added together. J15 is currently white text to hide it from the end user.
Everytime I add a new row of data I need the formula to update itself automatically. So for example, the next time I add a row of data the above formula has to change to
"=J15/11"
Or if I were to add three rows of data the formula would have been
"=J15/13"
View 9 Replies
View Related
Jun 20, 2014
I need VBA code to automatically send an email notification when data is added to worksheet. I am not very familiar with writing code and have been using other code from other posts. Ideally I would like it to send an email notification with the added information attached, in this case it would be Row # and Columns A thru O.
View 1 Replies
View Related
Sep 26, 2011
I am using pivot tables/charts for drilldown reporting. After running a complex macro to consolidate multiple reports into one range, I need to generate pivot tables and charts.
These reports will change month to month in the number of entries they have (template will be identical), and therefore my data source range for the pivot table is bound to change. Therefore my question is, how to develop a macro that will dynamically change the source data range to the amount of rows?
View 5 Replies
View Related
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
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
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
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
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
View Related
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
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
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
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
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
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
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
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
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
