Add Data Monthly And Then Have To Manually Update The "source Data" To Reflect The Added Data On Chart
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.
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.
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?
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!
I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
Sub UpdateItem ... .Fields.Item(1).value = activecell 'activecell value = "Joseph" If Not isempty(activecell.offset(0,1)) Then .Fields.Item(2).value = activecell.offset(0,1).value Else .Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works End If ... End Sub
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty ' or .Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
I have several ComboBoxes & ListBoxes on a Userform that populate by referring to Dynamic Named Range in the RowSource property. Also on the Userform is an area to edit/add/delete items in these named ranges. If I Add or Delete an item, the Named Range does not automatically resize--or at least the Menus don't "resize" accordingly--unless I unload the UserForm and load it back up. Is there a way to refresh these Comboboxes without reloading the UserForm?
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.
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.
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?
What I've got is 2 worksheets. One is updated every month with new data and therefore the style of the new worksheet is likely to change.
The other one is static and needs to take the data from the updated one. I can't just use a basic =sheet1!A1 because the data will get moved around.
I need to be able to tell it to: look in the new sheet, based off the column heading and row heading find the cell and copy it to the same place on the static sheet. e.g.
In G14, I need it to find on the second sheet "Apr 08" as the column heading and "Yell.com" as the row heading and put it in G14.
It would be easy to do it manually, but as the spreadsheet grows, it'll be harder and harder.
I have a graph that is showing the date on "Y" axis and a value on "X" axis, when I add a new date and value to my data the graph does not update, it just shows the data when I first created the graph.
How do I get the graph to display the new data I entered ?
I created a line graph by clicking on the "A" in column "A" (Thats where my date is) then clicking on the "B" in column "B" (Thats where my values are) and clicking the create graph button then clicked finished.
I have a pivot table I am try to update. The table references another tab where my data sheet exists. If I add data to the datasheet how can I get my pivot table to recognize the additional information.
consolidating data from 3 worksheets which contain same data fields but each representing 1 brand. I like to have a summary/mastersheet to contain data from all 3 worksheets and then when the data in each worksheet is added or updated, the summary worksheet should reflect the changes (adding new data to the last row).
I have a pivot table whose data source is located on a shared network drive. Each day a new data source is uploaded and the only thing that changes is the date in the filename (ie. DataSource080813.xlsx). I am trying to find a way to set the data source to update with todays date automatically each day so that when you open the workbook it is always current days data. I have a cell (B2) with a formula in it that gives me the file location of todays file (ie. X:TeamFolderFile1DataSource080813.xlsx, where the date is derived using the TODAY() function. Is there a way to set the pivot table data source to cell B2 and have it use the text from B2 as the file location?
I have entered all the formulas into my table (sheet 1) referenced to the source data but I want to upadate the source data regularly but when I delete sheet 2 all the formulas come up with #Ref error. how i can update my source data so my table automatically updates
I would like not to have to update the source data for the pivot table in my database. I've read that you can solve this by using a Dynamic Named Range and using that as the source for the pivot table. That way the pivot table will expand as new data is added to the database. The formula I used to create the DNR is:
The spreadsheet is comprised of 4 sales data sheets, one for each store. Each of these sales data sheets runs several pivot tables (One for dept sales, one for waste, one for sales on Selected UPCs etc) and I used to have 1 copy of each pivot table for each store (ie 24 pivot tables). Now I have found a way to have only 1 copy of each PivotTable which changes based on which store is selected using the following code:
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.
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.
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?
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.
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.
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.
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.
how to make all cells displaying an item from the list source update with new information added to list source.
Example: Cell "A1" on worksheet 2 displays the 3rd slot of information from list source on worsheet 5. I want cell "A1" on worksheet 2 to update when the list source information in the 3rd slot on worksheet 5 is changed.
Notes: The list is set up by naming the columns on Worksheet 5 and is set as a validation list on the other worksheets. So on Worksheet 2 column "A" has the validation
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".
I have a report that was created for 2005 that contains two worksheets: a "source data" worksheet and a " pivot table" worksheet. I cleared out the 2005 data in the "source data" worksheet and replaced it with 2006 data...after this I refreshed the Pivot Table and everything seemed fine. When looking at the file size I noticed that it was almost twice its original size....upon further investigation I found that the Pivot Table was internally holding onto the old source data (the "Show" functionality of the rows/columns in the table lists the 2005 row/column headers as well as the 2006 headers....even though no data from 2005 is shown in the Pivot Table).
Does anyone know how to purge the old data from the internal Pivot Table memory?
I hope this is enough information....let me know if you need more.
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),
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.
