Graph Based On Changing Range
Sep 4, 2006
I am trying to automate a graph for data that is not always the same amount but always in the same format. I have attached a spreadsheet that has an example of the data.
Columns B, D, & E are the ones that I am interested in charting in a line with markers type graph, but the rows may be 100 for one set of data and 1000 rows for another set of data. I am trying to get the macro to open a new worksheet and as part of the macro create a userform that will ask for the text information on the top of the worksheet (as this is not always correct when imported).
View 5 Replies
ADVERTISEMENT
Sep 8, 2006
I am trying to create a graph for a range of data that updates monthly (adding an extra month each time). I wanted the graph source data to update automatically each time the data is refreshed so used an OFFSET formula to identify a named range. I then point the graph to the named range as the source data.
When I enter the range as the source data the graph picks it up. However, when I re-enter the source data option on the graph it has converted the named range into a cell written range (ie. replaces "=QUALITY" with "='Front page'!$B$7:$J$10" - which therefore will not update when the range increases.
View 4 Replies
View Related
May 28, 2008
I have a vast database where I have linked charts. I send the data from a macro to the database and sometimes there are 1000 inputs and sometimes there are only 20.
How would i set up my charts to only graph the inputs that are present?
View 7 Replies
View Related
Nov 19, 2009
I'm trying to write some code to make an existing chart display the correct series of data. However, on the ".SeriesCollection..." lines, it gives me the error "Object does not support this property or method." I'm using a Bar-Line graph, if it makes any difference.
View 5 Replies
View Related
Oct 20, 2008
I have been tasked with creating a scheduling system for my work. There are three groups of shifts that can be selected, Opens, Swings, and Closes. I have been asked to make the cells turn red if a user selects a sequence of shifts. We are trying to avoid having a person working three scenarios:
Close>Swing>Open
Close>Off>Open
Close>Open
If they select the shift that meet this scenario, then I need the cells to turn red. The complete list of shifts are on the "Data" tab and the the three scenarios have been inputted into the "Jan" tab in E13:G15.
I am using the code below to change the background of the cells in my worksheet based on what is in the cell. How can I modify it to accomplish the changes below and meet the criteria above?
View 12 Replies
View Related
Apr 8, 2012
I am able to change the X-Axis with:
Code:
ActiveChart.Axes(xlCategory).TicketLables.Orientation = 45
How I change the Y-Axis?
View 1 Replies
View Related
Mar 27, 2012
I would like to change the color of my text on my X-axis of a graph. I would like to have 4 or 5 different colors to identify different categories. I see how I can change all the text but how would I change just the text in column 1-3 to red, column 4-7 to blue, etc...
View 2 Replies
View Related
Jun 17, 2007
I have a normal line graph I would like to see plotted vertically rather than the normal horizontal layout. In other words, points/lines would appear around a vertical axis and go from side to side. The normal graph would look as if it was tipped on its left side.
View 9 Replies
View Related
Mar 26, 2014
I have hundreds of column graphs, how do I quickly change the size of them to the exact same size of 8.1cm height and 26.4cm width.
View 1 Replies
View Related
Apr 25, 2008
I have a database in excel with a lot of tables in which lets a uses a combo box so the user select can view just one table.
I want to be able to create a macro so when a button is pressed, it copies the data into a new workbook and creates a chart so they can do whatever they want without disrupting anything in the original workbook.
I have been able to find code that will export the data and create a chart (see below) but the problem is the range changes from table to table so I need to find a way of changing how the chart picks up the data range.
Sub Chart_New_Book()
Sheets("Temp").Select
Cells.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Range("A1").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C10")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub
So at the moment when charting ony cells A1:C10 will be plotted no matter what the size of the table is and making the target range bigger only sees the blank cells being plotted.
View 3 Replies
View Related
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
Aug 28, 2012
I work with a team of users that are continually publishing reports in Word that contain charts and graphs copy and pasted from Excel into Word 2010.
We have a custom script that leverages a PDF engine to automatically convert .doc files to .PDF files that we distribute electronically to our clients. This all works great, but only if all my users select 'paste special' and Enhanced Metafile Format when adding their excel charts into our reports. Most of these people aren't tech savvy, and I'm havin ga hell of a time getting them to follow this workflow and am hoping there's a way in Office 2010 to select the default paste from excel into word when the content is a chart.
It seems like the default paste from excel is an embedded chart/graph that you can then further manipulate each component of the chart in Word; the default doesnt' paste an actual image. I am assuming the pdf renderer is using a lower resolution .PNG version of the image and when these are scaled for print and or pdf, they look like crap.
Is htere anyway I can automatically change the default paste format for the chart from excel into word to be an EMF/EMV (enhanced metafile?) Either thorugh the registry or some other saveable setting?
View 1 Replies
View Related
Sep 16, 2009
This may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
View 2 Replies
View Related
May 4, 2009
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"
and also t = time()
View 7 Replies
View Related
Apr 28, 2008
I have a table that has formulas that read off a pivot table. When the pivot table is updated, the other non-pivot table is updated. I created a graph off of the non-pivot table data. I want to do a bar chart, but if data is added to the pivot table (and then through formulas added to my non-pivot table) I want the graph to automatically update with the new fields. I can't figure out how to do this
For example, if my table had Grapes, apples and peaches and their quantities in cell A2:b4 if I choose range a2:b5 I don't get any data in my graph at all, if I choose the correct range a2:b4, I get a bar chart with Grapes, Apples and Peaches and thier quantites. If I added Strawberries to the Pivot table, the non-pivot table would update, but the graph is not grabbing row 5, I have to manually change the range to a2:b5.
View 9 Replies
View Related
Feb 1, 2010
i have a range (A2:A30) that has values imported into it (sheet1).
in B2:B30, i'm doing a simple calculation based on the perspective values in A.
The number of values imported into A is different each time my model is run.
i'd like to graph A and B columns against each other but i don't want to have to adjust the range on the graph each time the model is run.
ps. i tried graphing A2:A30 vs. B2:B30 but the graph also shows the empty cells in A so that doesn't work.
View 9 Replies
View Related
Feb 12, 2014
This is probably very simple but I'm having a problem finding how to change the color of a bar based on the value. For example, when the bar reaches 100% I want the bar to turn green while the other bars in the graph stay the same.
View 2 Replies
View Related
Jan 13, 2014
I am making a dynamic graph with use of listboxes. Please see attached example.
In the example doc is a graph which is now static. Based on the rows I selected in the table, for store 1 and for february.
What I'd like is that a user can select a store and a month in 2 listboxes and that the graph is dynamically created based on dynamic selection in the table. I think I need to use the OFFSET function and some defined names but I'm not sure how.
Example.xlsx
View 3 Replies
View Related
Oct 26, 2012
I have a question about creating a line graph, here's the scenario:
Once per week I update our inventory quantity in cells B1, B2, B3. I'd like to show these quantities on a graph which compares the quantity with the date. When the quantity gets updated it would automatically add week 2, when the quantity gets updated again it would again expand the graph and add week 3. Is this even possible?
Week 1
A B
Widget 2
Hammer 1
Saw 6
Week 2
A B
Widget 4
Hammer 1
Saw 6
View 2 Replies
View Related
Jan 6, 2010
I'm trying to select a range that will be changing by column. I'm not sure why my syntax isn't working. What I've got:
View 2 Replies
View Related
Oct 16, 2009
Is it possible to plot an xy scatter graph of "Result A" versus "Result B", and have the data points labelled with the values under "Mix" ?
Mix Result AResult B
1340.543 0.520
1360.562 0.525
1380.570 0.526
1400.561 0.528
1410.559 0.526
1420.568 0.526
1440.570 0.526
1460.578 0.526
1470.579 0.530
1480.575 0.529
View 2 Replies
View Related
May 13, 2014
I have a report that has rolling week by week data (as in YTD). The graphs I have are based on a 13 week period. As I add a week (done by VBA import) I want to update graphs to lastest 13 weeks as in last 13 columns of data on data sheet.
View 4 Replies
View Related
Jan 5, 2010
I would like to chart (pie or graph) sales based on the hour they occured.
In excel, I have column A - the time stamp, and column B, the sale. Note that each sale has its own unique time stamp.
View 9 Replies
View Related
Jul 7, 2006
Need the code to make a graph (bar diagram) based on the only the rows with numerical data in a column, where there is also blanks and NA. The graph I would like to make and the data-structure are shown in the file Graphs.xls. When updating the graph later on the cells with data may have changed (i.e. new rows with data may have been added)
View 4 Replies
View Related
May 9, 2008
i have a graph that i've linked to let's say cells A1:C150
however, my range could sometimes needs to be shrunken to A1:A100 or A1:A40 etc. (the number of rows that are "active" is determined by cell K27)
i'd like for my graph to automatically adjust depending on the number of rows that are filled within the range above. can this be done with formulas and graph manipulations?
i've put in a formula currently that makes the value of the cells that would return 0 into "" but that doesn't seem to do the trick.
View 9 Replies
View Related
Jul 23, 2008
I need to select data from columns A, B and I for the graph. I have the below code but get an error message. Can anybody help ?
Set cellGR1 = Cells(2, 1)
Set cellGR2 = Cells(K, 2)
Set cellGR3 = Cells(2, 10)
Set cellGR4 = Cells(K, 10)
Charts.Add ....
View 9 Replies
View Related
Aug 21, 2008
I have a Line Graph with 2 lines on it. Is it possible to write a macro to extend the range (Source range) of one of the lines ? I would also like to add a Data Label to the newly added point.
For e.g. If I have Line 1 graph only till 65, I want the macro to extend the range by one row to include 95 and also display a datalabel ......
View 9 Replies
View Related
Aug 29, 2012
A line graph is using a data row to plot the line; works fine. The data series ranges have been named for Week1 (H10), Week1_2 (H10:I10), Week1_3 (H10:J10), Week1_4 (H10:K10), Week1_5 (H10:L10), Week1_6 (H10:M10), Week1_7 (H10:N10), ...etc.
This is the data series formula name example to plot the data series from week 1 through week 7; ='Product Plan Q3 Week 7.xlsx'!Q3Wk1_7.
The series name formula Q3Wk1_7 for week 1 through week 7 is =$H$10:$N$10.
When this name is manually entered as a Series Value for the line graph, the graph plots exactly the way it is expected. The same is true if the Series Value is manually modified for some other ranges such as Week 1 through Week 9 (='Product Plan Q3 Week 7.xlsx'!Q3Wk1_9).
My challenge is to enter this Series Value based on the manually entered week number elsewhere; i.e., we're in week 7, the Series Value should be ='Product Plan Q3 Week 7.xlsx'!Q3Wk1_7. I've been trying to use an HLOOKUP table to select the correct Series Value formula based on the week number. The HLOOKUP works fine resulting in selecting the correct formula. I can also Name the range of data I want to graph and copy the name into the Series Value field with successful results. How do I get such a formula or Named range into the Series Value field without type it in? I want to set that Series Value to a range or name without having to select the line to manually enter the value.
View 3 Replies
View Related
Aug 5, 2013
I'm trying to create a line graph to show the trends of usage. I have a list of dates that I pulled from a website. I am trying to create a function that will count the number of dates between, for example, 8/5/2013 and 8/11/2013.
I had tried using this function to create a set of weekly ranges. =TEXT(DATE(2013, 1, 7)+(ROW(2:2)-1)*7, "m/d/yyyy h:mm AM/PM")&"-"&TEXT(DATE(2013, 1, 7)+(ROW(2:2)-1)*7+6,"m/d/yyyy h:mm AM/PM")
It correctly displays the date ranges, however when using this function:
=COUNTIF($E$2:$E$305, A2) Nothing gets pulled.
Here is one of the dates that has been pulled for example: 8/5/2013 11:10:00 AM
View 5 Replies
View Related
Dec 8, 2011
did in [URL] but my issue is complicated by not knowing what cell I need to move the graph to.
I am using Excel 2010 and I am a realitive novice at writing VBA code.
In My spreadsheet, I have a list of properties and some related cost info. From week to week, the number of properties varies. Some days I will have 6 properties and others I could have 100. Because of this, I need to make all of my references relative to other cells. I am having trouble repositioning the graphs that I have created from the data to be 2 cells below the label I have created for the graph (which is a merged cell covering Columns B:I on a row 3 rows below the last property in the report.
So far, I have:
Dim r1 As Range, r2 As Range, GraphRange As Range
Cells(2, 1).Select
Selection.End(xlDown).Select
[Code]....
View 1 Replies
View Related
