VBA - How To Create Graph Where Data Source Is Two Columns
Jan 9, 2012
I'm looking to create a graph where the data source is two columns:
Column A (From A2 down to last but one populated cell)
Column ? (Last populated column on the right, from ?2 down to last but one populated cell.
all I have at the moment is the generic code for creating a graph (which I reverse engineered from a macro I recorded).
Code:
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("1_bth_x_wk").Range("A1:H40"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="1_bth_x_wk"
View 9 Replies
ADVERTISEMENT
Sep 27, 2011
I am trying to create a line graph that will incorporate multiple columns of data in one series of data. The reason I do not place all of the data in one column is because it could exceed the maximum amount of rows allowed in excel. Also I need the data split up for viewing purposes.
I can easily just graph one column but how do I combine all the columns into one line graph with the data being in separate columns. Basically all the columns will be my Y values and X values are just 1:n.
Example Below:
Column AColumn B Column C159261037114812
Now in the example all of the values are x values.
View 2 Replies
View Related
Oct 19, 2011
Is it possible to produce a dynamic bar graph for instance where if I click on a bar it drills down and displays the source data?
View 1 Replies
View Related
Apr 17, 2013
I have an 'in-cell dropdown' which looks up two values, Q1 and Q2 I have a range of data by month showing a specific number as below e.g
Month
Count
2012-01
120
2012-02
190
2012-03
200
2012-04
150
2013-05
175
I have a Line graph using this entire table as the source data. What I would like to do is only use part of the data based on what the dropdown cell specifies. For example, if it is set to "Q1", I would like just the 2012-01 and 2012-02 data to show in the graph. If its "Q2", I would like only 2013-03 and 2012-04 data to show in the graph.
View 4 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
Apr 3, 2007
I have an excel worksheet that contains two sets of data. These numbers are then plotted onto a line graph to show the two sets of data against each other.
The graph is working fine, but only for a specific amount of data. The data is split into mutiple lines. From cells A1 all the way to BI1 is one row. I then have information in A4 to BI4. I would like to plot this information on a graph as a single continuous line. Is this possible?
Basically, My graph source is currently =Data!$A$2:$BI$3. This gives me two lines that I want. How do I get the data from A4:BI4 to join up to the first line in the graph.
View 5 Replies
View Related
Feb 26, 2014
Data or a case that can't be created a 3D bar graph from? I've been told there is just a little of data that can NEVER create 3D bar graph.
View 2 Replies
View Related
Sep 7, 2009
Given a pivot table, is there any way that I can determine the source data that was used to create the pivot table?
I suspect that the pivot table was created using only part of the data, but I'm not sure.
View 9 Replies
View Related
May 11, 2009
I have a spreadsheet called contacts which is as it says, I have another sheet called project management, I want to have a drop down list which refers back to the contacts spreadsheet, it this possible as they are two different files?
View 4 Replies
View Related
Sep 29, 2011
I have used sumif statement to set up a value by week and then used the drop down list cell reference to display what i want ie week 25 data
What I would like is to display the 8 previous weeks and then use this in a graph, so every time i change the drop down week I see all the data showing the current week and previous weeks.
Now the problem is which formula do i use to to identify previous weeks
ie
WK No 21 22 23 24 25
Visits 100 200 250 300 400
View 1 Replies
View Related
Jan 6, 2012
Ok, so here's the trick:
I know how to create combo or mixed graphs with line/bars on single axis, and secondary axis. I even know how to do mixed stacked and unstacked columnar charts...
But how do you depict in single view graph
a single bar (1 datapoint) with a single line (upper control limit e.g. target).?
And I don't want to to use shapes to draw the target line.
I want the target line to be automatically plotted by Excel...
View 9 Replies
View Related
Apr 26, 2012
I have created a pivot table in a workbook which relates to data from 2011 - 2012 and this works perfectly. What I now need to do is to copy the pivot table (without the data source) to a new workbook for data which will be collected from 2012 - 2013. Unfortunately when I copy it and try and find the new data source it does not update the filters according to the data in the new workbook.
View 5 Replies
View Related
Jul 4, 2006
I want to do is use a userform to automatically create a graph from the data that is shown in the worksheet. The data in the work sheet will grow and shrink all the time. Am i right in thinking I am meant to create a dynamic range? I know how to do it but am not too sure what I am meant to be naming. And secondly I am not too sure how I set up the button to produce the graph.
View 4 Replies
View Related
Nov 28, 2006
The "Sum" sheet can change its number of rows. The pivot table is based on it. I'm having trouble with the SourceData portion of the code in my macro ....
View 9 Replies
View Related
Apr 23, 2008
Looking for code that returns the cell address of a series in a pre-existing graph. I have a graph that already has Source Data manually assigned to it. Is there a way to find just the cell values or location of a single Series in a SeriesCollection object? I know that in order to assign a series of data to a Series object, you do something like this:
ActiveChart.SeriesCollection(1).Values = Worksheets("My Worksheet").Range("A1:F1")
The issue I am tackling with is a graph that already has preexisting data in the Series object, and I just need a quick and easy way to find the address of this data set.
View 3 Replies
View Related
Jul 17, 2009
I wish to hide some columns that contain data used to update a pivot table in another worksheet. What I want to know is though, will this affect the pivot table? I think that graphs in excel will normally ignore data in hidden cells, so I was wondering if it was the same with pivot tables.
View 2 Replies
View Related
Feb 4, 2014
I get large data sets that are organized in columns and each has a title at the top of it. I will get several sets of this data in order to compare them all on graphs.
Now here is the kicker, the columns are not always in the same order. Not always named the same. And not always the same number of columns.
I want to make a tool to handle this to a point where I just import the data and everything is taken care of. I also want a printable report for each data set to which I can select from a drop down menu. Populate cells on a tab, and then can be printed.
View 2 Replies
View Related
Sep 7, 2006
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.
Thanks in advance for any help,
Jon
View 9 Replies
View Related
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
Dec 29, 2011
I have a spreadsheet created in Excel 2003 (which is what we use at work, unfortunately).
My employees periodically take a test to ensure they have certain items memorized (or are making progress to that end). The spreadsheet rows show all 46 of my employees, and their test scores. The columns are the dates that the tests are administered. I can create a line graph based on the chart data, and interpolate these data with no problems.
The problem is that there are 46 employees! 46 lines on the same graph make for a very cluttered, hard to understand visual. I want to simplify the view by "filtering out" some of the data.
I have an additional column in my spreadsheet for each employee's work area (Area 1, Area 2, etc), and another column with data based on first letter of last name (the values here could be "A-G", "H-M", "N-S" and "T-Z", for example). I figure i could filter my line graph based on these two columns. For example, somehow select just Area 1, and reduce the number of lines on the graph to 16. Or better yet, Choose "Area 2" AND "A-M" and end up with 7 employees (and therefore 7 lines on the graph).
Here's what i have tried:
1) Select the work area column, and use the Filter, which created a drop-down list at the column heading. When i use this drop-down list, i can easily filter the data in the worksheet by Work Area, but this is not reflected in the line graph, which still shows all 46 lines. The problem was that i forgot that i had set the Calculation Options to "Manual". Setting this to "Automatic" (or leaving it on Manual and pressing F9) solved the problem, as the chart now updates when i use the filters. Calculation options are under the "Formula" tab in 2007, or in Tools -> Options -> [either calculation or formula, i forget what it's called] in 2003.
2) Create several separate line graphs in several separate sheets. I wouldn't want to assign someone else the task of maintaining a spreadsheet of such inefficient design.
View 1 Replies
View Related
Sep 28, 2004
Trying to create an N1.85 graph in Excel that has irregular spaced tick marks on the X-axis. Found the following information but no luck with it. Need US measure for this semi-log (10 X N1.85) graph. Also called a semi-expo (Q1.85) graph.
The resulting graph appears to be a log graph in reverse with one scale; the column widths are smaller at the left and become larger as they progress to the right.
Info found:
A 1.85 graph can be constructed manually by establishing a series of 15 values (in the case of the example in D5.2.1) from a base measurement to the exponent of 1.85.
Step 1
Select a base measurement for the desired size of the graph. A base measurement of 1.0 mm will produce a graph to 15 which is approximately 150 mm wide; a base measurement of 1.5 mm will produce a graph approximately 300 mm wide. In the case of a 1 mm base measurement, the x-axis numbers will be the 1-15 series. In the case of a base of 1.5 mm, the numbers will be represented by the series: 1.5, 3.0, 4.5, 6.0 etc. for 15 values.
Step 2
Construct a series of columns to the 1.85 exponent values measured from the zero point. The rows representing the pressure values are linear.
NOTE - A good approximation of the above can be computer-generated by a spreadsheet programme by entering a column width established from the exponential figures by subtracting the preceding value in each case. The column dimensions are displayed in the number of standard characters able to be accommodated in the column width which is slightly inaccurate in linear dimension.
The figures below indicate the values for a graph based on 1.0 mm.
Linear scale Exponential value of linear values = Column width = linear values to 1.85 power exponential value - preceding value
1 1 1
2 3.61 2.61
3 7.63 4.03
4 13.00 5.36
5 19.64 6.64
[Code]....
View 6 Replies
View Related
Oct 14, 2008
I want to create excel graph using VBA and found difficulties on chart name set to default.
the script is like this :
ActiveSheet.ChartObjects("Chart 31").Activate
Can I set my own name of the chart instead of "Chart 31" (excel default) ?
View 9 Replies
View Related
Feb 23, 2009
I have one excel file with several columns and rows of data.
Then I have a template excel with titles and some tables (no formulas, no macros, etc).
I need to generate a new workbook (based on the template) for each row of data from the source excel.
View 9 Replies
View Related
Nov 30, 2005
What can I have the IF statement return as a result that will NOT graph at all. Just leave a hole in the graph.
Long Question:
I have a large range of values and dates.
1/1 1/2 1/3 ....etc
10 11 7 .......etc
8 12 6 .....etc
These values are pulled via VLOOKUP() from various places. When VLOOKUP finds a blank cell, it returns a '0', which doesn't work for me. I've added an IF(ISBLANK), to return "", which is (I believe) an empty cell. The problem is, this is still graphing as a zero. So it will be graphing along nicely, and then shoot down to zero and back up again.
View 9 Replies
View Related
Jul 6, 2009
Rep NameCall StartedDuration h:mm:ssRep 12:19:18 PM0:00:36Rep 13:09:01 PM0:00:56Rep 13:11:01 PM0:01:05Rep 13:12:12 PM0:01:13Rep 13:26:26 PM0:00:39Rep 27:08:35 AM0:01:57Rep 27:16:32 AM0:02:12Rep 21:47:17 PM0:02:01Rep 37:05:01 AM0:10:37Rep 37:36:24 AM0:00:16Rep 311:41:43 AM0:15:21Rep 312:47:36 PM0:00:46Rep 31:45:36 PM0:02:19Rep 31:49:54 PM0:00:32Rep 31:51:46 PM0:00:56Rep 31:55:03 PM0:00:35Rep 31:59:26 PM0:00:22Rep 47:04:24 AM0:00:16Rep 42:40:41 PM0:00:08Rep 43:06:22 PM0:02:16Rep 43:21:35 PM0:00:04Rep 43:21:49 PM0:03:05
which is the time that any rep started a call and the duration of each call
and i need to make a graph from it that kind of looks like this:
(not actual data)
this way you can clearly see that for the most part rep 3 was the most productive however only rep 2 was on a call after 2pm.
i need this report to see tendencies on how much time is wasted on meetings and such keeping the reps off the phone.
View 9 Replies
View Related
Mar 12, 2008
How can I create a box chart showing graphically a median, 1st and 3rd quartile and a minimum and a maximum?
View 5 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
Jun 28, 2006
I am wanting to create a yearly graph, but it isn't allowing me to add cells from multiple worksheets.
View 1 Replies
View Related
May 29, 2008
I need to plot a stacked chart. I have attached a sample of it. I need to plot, category As X and subcategory and and its percentage as Y (stacked chart). In the attached sample I plotted a chart. The problem is, the series name showing is not correct. It takes the series name from the top row.
View 5 Replies
View Related
Apr 16, 2014
I have a table of data which contains company names, the number of high risk policies, number of low risk policies, and the percentage of high risk policies (compared to total policies).
What I want to do is for people to be able to select up to six of these companies and press a button that says 'create graph' and it creates a graph which shows the number of high risk and low risk in a stacked bar, and the percentage on a separate axis as a line. I know how to manually create this graph no problem, but to be able to dynamically create one from selected companies would be awesome.
To start with I have created six drop downs where you can select the company name as I image the macro will need to know which companies' data to look for in the source table.
View 3 Replies
View Related
