Growing list of chart data
Solved/Closed
Trowa
-
Mar 23, 2010 at 09:49 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 8, 2010 at 09:07 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 8, 2010 at 09:07 AM
Related:
- Growing list of chart data
- Amd crossfire compatibility chart - Guide
- Chart gpt download - Download - Other
- My contacts list names - Guide
- Counter strike 1.6 cheats list - Guide
- Display two columns in data validation list but return only one - Guide
14 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 23, 2010 at 12:20 PM
Mar 23, 2010 at 12:20 PM
Trowa how about some thing like this
maxrows = cells(rows.count, "L").end(xlup).row
Names.Add Name:="GrafiekData", RefersTo:="=Feestdagen data!K3:L" & maxrows
There is another way too, where you dont have to use name. See this for that
https://ccm.net/forum/affich-281219-changing-cell-ranges-in-all-series-of-a-graph
basic idea is to use
ActiveChart.SeriesCollection(1).Formula = "=Series(....."
maxrows = cells(rows.count, "L").end(xlup).row
Names.Add Name:="GrafiekData", RefersTo:="=Feestdagen data!K3:L" & maxrows
There is another way too, where you dont have to use name. See this for that
https://ccm.net/forum/affich-281219-changing-cell-ranges-in-all-series-of-a-graph
basic idea is to use
ActiveChart.SeriesCollection(1).Formula = "=Series(....."
Hi Rizvisa,
The second solution is a bit to complicated for me and it looked to me that Sisa has a couple of fixed ranges, where I have one growing range.
The first solution needs a little adjustment for it to work.:
Since the sheet "Feestdagen data" already contains a lot of data, I have compiled the chart data on the chart sheet (called "Grafiek"), so that it doesn't interfere with the row count.
Data is now in range A42:B??.
The problem is that the chart data is being pulled from a different sheet via formula, thus not being an empty cell:
Cell A42: =IF('Voltooide opdrachten'!M2=0;"";'Voltooide opdrachten'!M2)
Cell B42: =IF('Voltooide opdrachten'!M2=0;"";'Voltooide opdrachten'!M2)
These formula's are dragged down untill row 1100.
The created named has a range of A42:B1100.
How do I make the code to look for empty values instead of empty cells?
Thanks in advance,
Trowa
The second solution is a bit to complicated for me and it looked to me that Sisa has a couple of fixed ranges, where I have one growing range.
The first solution needs a little adjustment for it to work.:
Since the sheet "Feestdagen data" already contains a lot of data, I have compiled the chart data on the chart sheet (called "Grafiek"), so that it doesn't interfere with the row count.
Data is now in range A42:B??.
The problem is that the chart data is being pulled from a different sheet via formula, thus not being an empty cell:
Cell A42: =IF('Voltooide opdrachten'!M2=0;"";'Voltooide opdrachten'!M2)
Cell B42: =IF('Voltooide opdrachten'!M2=0;"";'Voltooide opdrachten'!M2)
These formula's are dragged down untill row 1100.
The created named has a range of A42:B1100.
How do I make the code to look for empty values instead of empty cells?
Thanks in advance,
Trowa
Let me explain what I mean by "empty value", vs "empty cells":
When I run the macro:
The name "GrafiekData" is assigned to the range A42:B1100, because formulas fill those cells.
I want it to be assigned to the range A42:B46, because those cells contain the values I want to build a graph of.
So apparently the macro looks for cells which aren't empty and gives that range a name.
But I want the macro to look for cells with values and give that range a name.
Let me post my workbook and explain what I would like to see happen:
Sheet "Alle opdrachten" displays incoming delivery orders.
When the order has been delivered the line turns green.
At the moment I have put 5 green lines of orders to test with.
When the user clicks on the button "Voltooide opdrachten afvoeren" the green lines will be moved to the sheet "Voltooide opdrachten" to display a summary of what has been finished.
On the sheet "Voltooide opdrachten":
Column E holds the amounts which has been delivered.
Column M holds the date which the order has been finished on.
Out of these 2 columns I would like to create a graph.
I thought to create a graph the columns had to be next to each other.
So on sheet "Grafiek" starting on line 40 (so I can put the graph above it) I use formulas to pull the data from the sheet "Voltooide opdrachten".
Now I would like to create a standard column graph:
X axis shows dates.
Y axis shows amounts.
Note: I didn't create a graph yet, since I don't have a named range yet.
Here's the link:
https://authentification.site/files/21625935/Copy_of_PostPlanning.xls
I'm curious to see if you would go for your first or second solution.
Thanks in advance and kind regards,
Trowa
When I run the macro:
Sub GrafiekBereik() maxrows = Cells(Rows.Count, "B").End(xlUp).Row Names.Add Name:="GrafiekData", RefersTo:="=Grafiek!A42:B" & maxrows End Sub
The name "GrafiekData" is assigned to the range A42:B1100, because formulas fill those cells.
I want it to be assigned to the range A42:B46, because those cells contain the values I want to build a graph of.
So apparently the macro looks for cells which aren't empty and gives that range a name.
But I want the macro to look for cells with values and give that range a name.
Let me post my workbook and explain what I would like to see happen:
Sheet "Alle opdrachten" displays incoming delivery orders.
When the order has been delivered the line turns green.
At the moment I have put 5 green lines of orders to test with.
When the user clicks on the button "Voltooide opdrachten afvoeren" the green lines will be moved to the sheet "Voltooide opdrachten" to display a summary of what has been finished.
On the sheet "Voltooide opdrachten":
Column E holds the amounts which has been delivered.
Column M holds the date which the order has been finished on.
Out of these 2 columns I would like to create a graph.
I thought to create a graph the columns had to be next to each other.
So on sheet "Grafiek" starting on line 40 (so I can put the graph above it) I use formulas to pull the data from the sheet "Voltooide opdrachten".
Now I would like to create a standard column graph:
X axis shows dates.
Y axis shows amounts.
Note: I didn't create a graph yet, since I don't have a named range yet.
Here's the link:
https://authentification.site/files/21625935/Copy_of_PostPlanning.xls
I'm curious to see if you would go for your first or second solution.
Thanks in advance and kind regards,
Trowa
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 26, 2010 at 04:18 PM
Mar 26, 2010 at 04:18 PM
The column do not have to be next to each other. They can be separate too When it comes to creating graph, I am novice. I will try to see if I can create one today. Stay tuned.
Didn't find the answer you are looking for?
Ask a question
Hi Rizvisa,
I don't want to sound impatient or disrespectfull, but are you still working on this?
Please take the time you need, just want to know if I can still expect an answer.
I don't want to sound impatient or disrespectfull, but are you still working on this?
Please take the time you need, just want to know if I can still expect an answer.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 30, 2010 at 09:20 AM
Mar 30, 2010 at 09:20 AM
Sorry man. It just slipped my mind. I will look into it today. My graph skills are very weak so it just might take a bit longer. Hopefully you are pro at graphs, so if you could just create a graph and post link, it will save me a bunch of time
Thanks for the reminder
Thanks for the reminder
I can imagine something might slip your mind since you're helping out so much people (props to you for doing so! [the helping part, not the slipping mind part :) ] ).
I'm just glad you're still helping me since graphs are not your strongpoint (according to yourself).
As you requested I've created a graph at the top of sheet "Grafiek". I used some example data which I put in the range D40:E47 of the same sheet "Grafiek".
Here is the link:
https://authentification.site/files/21738790/Copy_of_PostPlanning.xls
Kind regards,
Trowa
I'm just glad you're still helping me since graphs are not your strongpoint (according to yourself).
As you requested I've created a graph at the top of sheet "Grafiek". I used some example data which I put in the range D40:E47 of the same sheet "Grafiek".
Here is the link:
https://authentification.site/files/21738790/Copy_of_PostPlanning.xls
Kind regards,
Trowa
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 4, 2010 at 11:24 AM
Apr 4, 2010 at 11:24 AM
Trowa
Question for you
1. Will the graph always remain in the workbook and only each time just the data would change ?
2. You have data in Grafiek. From what I understood from you, this is the same data (all rows) that can be found in column E and M on sheet Voltooide opdrachten. Is there a reason that you cannot use the data on Voltooide opdrachten directly ?
Question for you
1. Will the graph always remain in the workbook and only each time just the data would change ?
2. You have data in Grafiek. From what I understood from you, this is the same data (all rows) that can be found in column E and M on sheet Voltooide opdrachten. Is there a reason that you cannot use the data on Voltooide opdrachten directly ?
1. Yes, the graph will remain in the workbook and only each time the data range will change.
2. Correct and there is no specific reason other then that I thought that it would be easier to put both columns next to each other in order to create the graph.
Kind regards,
Trowa
2. Correct and there is no specific reason other then that I thought that it would be easier to put both columns next to each other in order to create the graph.
Kind regards,
Trowa
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 6, 2010 at 09:08 AM
Apr 6, 2010 at 09:08 AM
Try this (assuming graph is already there)
This is relying on data from Voltooide opdrachten. How ever we can use the data from Grafiek. Due to formula, we cannot use End(xlUp).Row. For that either we loop thru each row to find out if the value is blank or not
In that case you can use this to find out the max rows
Other alternative would be that you use copy and pastespecial as values to eliminate formula and then you can use End(xlUp).Row to find the max rows
Personally I think either use of Voltooide opdrachten data directly or use of MaxUsedRows function will be better. Of course if you do use MaxUsedRows function, make sure that you change the code to reflect that you would be using data from Grafiek instead of Voltooide opdrachten
Sub RefreshGraph() Dim lMaxRows As Long Sheets("Voltooide opdrachten").Select lMaxRows = Cells(Rows.Count, "E").End(xlUp).Row Sheets("Grafiek").Select DoEvents ActiveSheet.ChartObjects("Chart 1").Select DoEvents ActiveChart.SeriesCollection(1).Formula = "=SERIES(""Data""" _ & ",'Voltooide opdrachten'!$M$2:$M$" & lMaxRows _ & ",'Voltooide opdrachten'!$E$2:$E$" & lMaxRows _ & ",1)" End Sub
This is relying on data from Voltooide opdrachten. How ever we can use the data from Grafiek. Due to formula, we cannot use End(xlUp).Row. For that either we loop thru each row to find out if the value is blank or not
In that case you can use this to find out the max rows
Function MaxUsedRows(Sheet As String, column As String) as long 'sample call lRows = MaxUsedRows("Grafiek","A") Dim lRows As Long Dim myRow As Long Sheets(Sheet).Select lRows = Cells(Rows.Count, column).End(xlUp).Row For myRow = lRows To 1 Step -1 If (Cells(myRow, column) <> "") Then Exit For Next If myRow = 0 Then myRow = 1 MaxUsedRows = myRow End Function
Other alternative would be that you use copy and pastespecial as values to eliminate formula and then you can use End(xlUp).Row to find the max rows
Personally I think either use of Voltooide opdrachten data directly or use of MaxUsedRows function will be better. Of course if you do use MaxUsedRows function, make sure that you change the code to reflect that you would be using data from Grafiek instead of Voltooide opdrachten
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 6, 2010 at 10:07 AM
Apr 6, 2010 at 10:07 AM
Also just one more thing,
I saw that you have code like
Range("A4:M65536").Select
I am presuming that 65536 is the max row in sheets. If last possible row is needed, it would be safer and more portable to use rows.count
So
Range("A4:M" & rows.count).Select
I do understand that 65536 is a big number, and in most cases we dont get to use all these rows, I just thought to give the suggestions as I myself used to do it in this very manner till I got to use 2007 and then all off sudden my last row was no longer 65536
I saw that you have code like
Range("A4:M65536").Select
I am presuming that 65536 is the max row in sheets. If last possible row is needed, it would be safer and more portable to use rows.count
So
Range("A4:M" & rows.count).Select
I do understand that 65536 is a big number, and in most cases we dont get to use all these rows, I just thought to give the suggestions as I myself used to do it in this very manner till I got to use 2007 and then all off sudden my last row was no longer 65536
Thanks Rizvisa, using your RefreshGraph code does exactly what I want.
Also thanks for explaining the use of rows.count.
Guess you can't say you are a graph novice anymore :) .
Kind regards,
Trowa
Also thanks for explaining the use of rows.count.
Guess you can't say you are a graph novice anymore :) .
Kind regards,
Trowa
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 8, 2010 at 09:07 AM
Apr 8, 2010 at 09:07 AM
You are very welcome Trowa. Glad it worked out fine. Sorry it took two weeks.
Nope I am still a dufus when it comes to creating graph. Once you gave me the graph, then I found myself on familiar ground. It sound so stupid but creating graphs is my weakest spot in excel.
Nope I am still a dufus when it comes to creating graph. Once you gave me the graph, then I found myself on familiar ground. It sound so stupid but creating graphs is my weakest spot in excel.